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
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
please provide wcf, and mvc and java script vedios
ReplyDeletehello sir
ReplyDeleteyour 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
Hi Madhu, very good question. No it's not mandatory. Even without these relationships you should be able to perform joins without any issues.
Deleteyes sir we can join tables with out relation ship is calld non equi joins
DeleteHow can we join two tables without any primary key/Foreign key?
DeleteIt is possible, try creating 2 tables without defining table relationship, enter data and execute the query as long as the relationship matches.
Deletefor 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)
NON EQUI JOINS means, joining operation performed using other than Equal sign (=) . those are (<, >, <=, >=, etc.,)
DeleteVenkat Sir, your videos are very useful. hats off to you for uploading the content for free (for us) in Youtube.
DeleteNot everyone does that in India.
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?
ReplyDeleteselect a.*, r.*
ReplyDeletefrom 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).
What if I don't want to allow join for a table?
ReplyDeleteHi Venkat, your videos are very useful. hats off to you for uploading the content for free (for us) in Youtube.
ReplyDeleteHi Venkat ,can you please give real time example of cross -join?
ReplyDeleteI Just came across your videos, excellent, i believe i can learn sql now
ReplyDeletei want exercise of all joins for practice .. Mr venkat
ReplyDeleteSir, can you please tell us join with multiple conditions
ReplyDeleteHi,
ReplyDeleteIt 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
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.
DeleteHi Venkat sir,
ReplyDeleteYour 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
yes, it,s correct @vignesh
DeleteHello Venkat,
ReplyDeleteWhy we are getting 7 rows in right join.
Rob Mike
Ben Mike
Sam Mike
NULL Rob
Mike Todd
NULL Ben
NULL Sam
Your tutorials are The Best in Industry
ReplyDeleteConsider a table “MonthlySales” with following Columns
ReplyDeleteCity| 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...
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