Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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

10 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
  5. First of all thanks to such wonderful explanatory videos.
    On that note, I have a question. Why these intelligent joins are used as this can be achieved without joining the tables when we actually do not need any information from the other table then just the filtering clause would do to achieve these results.
    Your response is greatly appreciated.
    Thanks
    Raja

    ReplyDelete
  6. In finding the not matching from left/right in the Venkat`s illustration. Where clause =NULL is used. I suppose this is considering the two tables related by foreign- primary key. How can we solve this if there is no foreign key relationship ? Or can i use where table1.id != talbe2.id ?

    ReplyDelete
  7. Hi Venkant, I want to know if table a has 10 rows and table b has 10 rows
    if we perform a left join what is the minimum record count and maximum record count? Could you explain it with other type of joins as well?

    ReplyDelete

It would be great if you can help share these free resources