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

24 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
  8. I Just came across your videos, excellent, i believe i can learn sql now

    ReplyDelete
  9. i want exercise of all joins for practice .. Mr venkat

    ReplyDelete
  10. Sir, can you please tell us join with multiple conditions

    ReplyDelete
  11. Hi,
    It would be very helpful for my understanding if some one can explain.
    We have only ManagerID in the Employee table and the Manager Name is in a different table which is not in the example above.
    In that case, why need to do a self join with employee table, as we can do a join with that Manager information table which also produce the desired result if we write a query selecting the desired columns for the output.
    Please correct me if my understanding is wrong.
    Regards,
    Raj

    ReplyDelete
    Replies
    1. Hi @enlightened minds, The employee has a manager who is also an employee. So the manager id is the employee id of another person who is in same table.

      Delete
  12. Hi Venkat sir,
    Your videos are excellent. I started learning it now. It is very useful and the way you explain it help us understand it better.

    I think here in the cross join query, the following query would be correct.

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

    ReplyDelete
  13. Hello Venkat,

    Why we are getting 7 rows in right join.
    Rob Mike
    Ben Mike
    Sam Mike
    NULL Rob
    Mike Todd
    NULL Ben
    NULL Sam

    ReplyDelete
  14. Your tutorials are The Best in Industry

    ReplyDelete
  15. Consider a table “MonthlySales” with following Columns
    City| Date| Customer_ID |
    Write a dynamic query to give months over month repeat customers. i.e, if 1000 customers shopped in Jan hen out of those thousand how many shopped again in Feb and similarly if 1500 customers shopped in Feb how many of them shopped again in March.

    Help me with this...

    ReplyDelete
  16. Venkat sir Advanced join concept is not opening, can you please see it and post it sir, looks like there is some issue with that

    ReplyDelete

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