Joins
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Joins as PDF for free.
More details
- Words: 930
- Pages: 8
SQL Joins One line Explanation - Retrieving data from multiple tables. Example: Employee table (tblEmployee) EmpId EmpName
EmpSalary
DeptId
ts200 ts201 ts202 ts203 ts204 ts205 ts206 ts207 ts208 ts209 ts210 ts211 ts212 ts213 ts214 ts300
23000 19000 16000 16000 15999 15999 14999 12999 13999 10999 5999 4999 3999 3999 3999 NULL
dt101 dt102 dt103 dt103 dt103 dt103 dt104 dt104 dt104 dt106 dt105 dt107 NULL NULL NULL NULL
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Department table (tblDepartment) DeptId DeptName dt101 dt102 dt103 dt104 dt105 dt106 dt107 dt108 dt109
Manager Leader Developer Tester Maintance Documenter Worker Cleaner Coffee Boy
Different type of Joins 1. Inner Joins 2. Outer Joins 1) Left Outer Joins 2) Right Outer Joins 3) Full Outer Joins 3. Self Joins 4. Cross Joins Example for Joins 1. Inner Joins Example: select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee , tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker
~~~~ default join as Inner Joins. ~~~ it's say like this select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee Join tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee inner Join tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId
2. Outer Joins Left Outer Joins A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table. Example: select e.EmpName,d.DeptName from tblEmployee e left outer join tblDepartment d on e.DeptId = d.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker NULL NULL NULL NULL
Right Outer Joins A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order. Example select e.EmpName,d.DeptName from tblEmployee e right outer join tblDepartment d on e.DeptId = d.DeptId
EmpNAme
DeptNAme
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Santhanam Madam Velu NULL NULL
Manager Leader Developer Developer Developer Developer Tester Tester Tester Maintance Documenter Worker Cleaner Coffee Boy
Full Outer Joins Combined left and right Outer joins Example select e.EmpName,d.DeptName from tblEmployee e full outer join tblDepartment d on e.DeptId = d.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah NULL NULL
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker NULL NULL NULL NULL Cleaner Coffee Boy
Cross Join A cross join (or Cartesian Product join) will return a result table where each row from the first table is combined with each row from the second table. Example select e.EmpName,d.DeptName from tblEmployee e cross join tblDepartment d EmpNAme Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier
DeptName Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Developer Developer
Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna
Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner
Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Cleaner Cleaner Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy
Self Joins A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. Example SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region FROM Customers AS c1, Customers AS c2 WHERE c1.Region = c2.Region AND c1.ContactName <> c2.ContactName ORDER BY c1.Region, c1.ContactName;
EmpSalary
DeptId
ts200 ts201 ts202 ts203 ts204 ts205 ts206 ts207 ts208 ts209 ts210 ts211 ts212 ts213 ts214 ts300
23000 19000 16000 16000 15999 15999 14999 12999 13999 10999 5999 4999 3999 3999 3999 NULL
dt101 dt102 dt103 dt103 dt103 dt103 dt104 dt104 dt104 dt106 dt105 dt107 NULL NULL NULL NULL
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Department table (tblDepartment) DeptId DeptName dt101 dt102 dt103 dt104 dt105 dt106 dt107 dt108 dt109
Manager Leader Developer Tester Maintance Documenter Worker Cleaner Coffee Boy
Different type of Joins 1. Inner Joins 2. Outer Joins 1) Left Outer Joins 2) Right Outer Joins 3) Full Outer Joins 3. Self Joins 4. Cross Joins Example for Joins 1. Inner Joins Example: select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee , tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker
~~~~ default join as Inner Joins. ~~~ it's say like this select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee Join tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId select tblEmployee.EmpName,tblDepartment.DeptName from tblEmployee inner Join tblDepartment on tblEmployee.DeptId = tblDepartment.DeptId
2. Outer Joins Left Outer Joins A left outer join will return all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table. Example: select e.EmpName,d.DeptName from tblEmployee e left outer join tblDepartment d on e.DeptId = d.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker NULL NULL NULL NULL
Right Outer Joins A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order. Example select e.EmpName,d.DeptName from tblEmployee e right outer join tblDepartment d on e.DeptId = d.DeptId
EmpNAme
DeptNAme
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Santhanam Madam Velu NULL NULL
Manager Leader Developer Developer Developer Developer Tester Tester Tester Maintance Documenter Worker Cleaner Coffee Boy
Full Outer Joins Combined left and right Outer joins Example select e.EmpName,d.DeptName from tblEmployee e full outer join tblDepartment d on e.DeptId = d.DeptId EmpName
DeptName
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah NULL NULL
Manager Leader Developer Developer Developer Developer Tester Tester Tester Documenter Maintance Worker NULL NULL NULL NULL Cleaner Coffee Boy
Cross Join A cross join (or Cartesian Product join) will return a result table where each row from the first table is combined with each row from the second table. Example select e.EmpName,d.DeptName from tblEmployee e cross join tblDepartment d EmpNAme Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier
DeptName Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Manager Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Leader Developer Developer
Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Developer Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Tester Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance Maintance
Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna
Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Documenter Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Worker Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner Cleaner
Raja Ram Hannah Vijay Fredy Xavier Venkatesh Siva Ganesh Anbu Mani Susila Hannah Pradeep Kasi Madam Santhanam Velu Raja Krishna Raja Ram Hannah
Cleaner Cleaner Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy Coffee Boy
Self Joins A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. Example SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region FROM Customers AS c1, Customers AS c2 WHERE c1.Region = c2.Region AND c1.ContactName <> c2.ContactName ORDER BY c1.Region, c1.ContactName;
