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

Self join in sql server - Part 14

In Part 12 of this video series we have learnt the basics of joins and in Part 13 we have learnt about advanced or intelligent joins. Please watch Parts 12 and 13 before watching this video
Part 12 - Basic joins
Part 13 - Advanced joins







In parts 12 and 13, we have seen joining 2 different tables - tblEmployees and tblDepartments. Have you ever thought of a need to join a table with itself. Consider tblEmployees table shown below.


Write a query which gives the following result.



Self Join Query:
A MANAGER is also an EMPLOYEE. Both the, EMPLOYEE and MANAGER rows, are present in the same table. Here we are joining tblEmployee with itself using different alias names, E for Employee and M for Manager. We are using LEFT JOIN, to get the rows with ManagerId NULL. You can see in the output TODD's record is also retrieved, but the MANAGER is NULL. If you replace LEFT JOIN with INNER JOIN, you will not get TODD's record.

Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Left Join tblEmployee M
On E.ManagerId = M.EmployeeId


In short, joining a table with itself is called as SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. The above query is, LEFT OUTER SELF Join.

Inner Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Inner Join tblEmployee M
On E.ManagerId = M.EmployeeId

Cross Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee
Cross Join tblEmployee

13 comments:

  1. please provide wcf, and mvc and java script vedios

    ReplyDelete
  2. hello sir
    your tutorials are excellent..Thank you so much for providing such a tutorials which helps us a lot...
    I have a small doubt...Is it necessary to maintain a relationship(such as primary key and foreign key ) between tables when we perform JOIN OPERATIONS

    ReplyDelete
    Replies
    1. Hi Madhu, very good question. No it's not mandatory. Even without these relationships you should be able to perform joins without any issues.

      Delete
    2. yes sir we can join tables with out relation ship is calld non equi joins

      Delete
    3. How can we join two tables without any primary key/Foreign key?

      Delete
    4. It is possible, try creating 2 tables without defining table relationship, enter data and execute the query as long as the relationship matches.
      for e.g:
      select * from Department as d, student as s
      where s.DepartmentID = d.Id

      Note: I am saying that I am not defining a relationship b/w the tables but the query will execute and provide the right result(s)

      Delete
    5. NON EQUI JOINS means, joining operation performed using other than Equal sign (=) . those are (<, >, <=, >=, etc.,)

      Delete
    6. Venkat Sir, your videos are very useful. hats off to you for uploading the content for free (for us) in Youtube.
      Not everyone does that in India.

      Delete
  3. I came across your tutorials on YouTube and they are by far the best and thus thank you. I have a question. I'm working with a client data base and am doing a left join between admission and most recent. However I striving to exclude all encounters between admission and recent and am stuck. Any suggestions?

    ReplyDelete
  4. select a.*, r.*
    from admission a
    join recent r
    on a.id = r.aId
    where a.id is null and r.aid is null

    i didn't get the meaning between admission and most recent. But i believe the above query should work (after using the proper table and field names).

    ReplyDelete
  5. What if I don't want to allow join for a table?

    ReplyDelete
  6. Hi Venkat, your videos are very useful. hats off to you for uploading the content for free (for us) in Youtube.

    ReplyDelete
  7. Hi Venkat ,can you please give real time example of cross -join?

    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.