Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Advanced Joins - Part 13

In this video session we will learn about
1. Advanced or intelligent joins in SQL Server
2. Retrieve only the non matching rows from the left table
3. Retrieve only the non matching rows from the right table
4. Retrieve only the non matching rows from both the left and right table

Before watching this video, please watch Part 12 - Joins in SQL Server







Considers Employees (tblEmployee) and Departments (tblDepartment) tables

Employee Table (tblEmployee)


Departments Table (tblDepartment)


How to retrieve only the non matching rows from the left table. The output should be as shown below:


Query:
SELECT       Name, Gender, Salary, DepartmentName
FROM           tblEmployee E
LEFT JOIN   tblDepartment D
ON                 E.DepartmentId = D.Id
WHERE        D.Id IS NULL



How to retrieve only the non matching rows from the right table


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM             tblEmployee E
RIGHT JOIN    tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL



How to retrieve only the non matching rows from both the left and right table. Matching rows should be eliminated.


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM              tblEmployee E
FULL JOIN      tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL
OR                   D.Id IS NULL

6 comments:

  1. hello sir ,
    i have a query i want to join two table one table party_table contain 4 column-- id |party_name | assign_user1 | assign_user2 | assign_user3 | assign_user4
    01| abc11 | 100 |102 | null | 103
    02| abc12 | 102 | 103 | 100 | null

    and and second table contain party name and code like this
    assigned_code | party_code
    100 | abc16
    102 | abc17

    i want to join both of the table and get the all party name , from both of the table of and assigned user ..

    is it possible to join one column from multiple column (check the favorable matching value )of another column and show in one table.
    is it possible ..

    ReplyDelete
    Replies
    1. @ALOK DEO, I think u cannot join the two tables you mentioned in the example because there is no common column in both the tables .When u have a common column in both tables u can get all the users using left join.
      CORRECT ME IF I AM WRONG !!

      Delete
  2. Hi Venkat,

    can you please explain how can we compare two tables in sql

    ReplyDelete
  3. Great tutorials, I have question though, I want to generate n number of rows in a table, how do I do it using sql (not using stored proc or function)
    n = 10 or 20 or ...

    ReplyDelete
    Replies
    1. Query to generate 20 rows without using stored proc or function .. try this...


      declare @Start int
      declare @End int
      set @Start = 1
      set @End = 20
      while(@Start <= @End)
      begin
      Select convert(varchar,@Start) + ' Row' as Rows
      Set @Start = @Start + 1
      end

      Delete
  4. Hi Anonymous June 1,2006 at 11.20 ... the Answer for ur question is as follows....
    Execute the below lines of code in sql .. u will 20 rows..

    declare @Start int
    declare @End int
    set @Start = 1
    set @End = 20
    while(@Start <= @End)
    begin
    Select cast(@Start as varchar) + ' Row' as Rows
    Set @Start = @Start + 1
    end

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.