Tuesday 11 July 2017

LEFT OUTER JOIN vs RIGHT OUTER JOIN


What are the differences between LEFT OUTER JOIN and RIGHT OUTER JOIN
[LEFT OUTER JOIN vs RIGHT OUTER JOIN] OR [LEFT JOIN vs RIGHT JOIN]?

LEFT JOIN
RIGHT JOIN
Use
Select all records from the LEFT table and matched records from RIGHT table.

NOTE:

LEFT JOIN and LEFT OUTER JOIN are the same; both are producing the same plan, performance and result.

Select all records from the RIGHT table and matched records from LEFT table.

NOTE:

RIGHT JOIN and RIGHT OUTER JOIN are the same; both are producing the same plan, performance and result.


An Example for LEFT JOIN and RIGHT JOIN

Table: Department

SELECT * FROM Department WITH (NOLOCK)

DepartmentID
DepartmentName
IsActive
1
Computer Science
1
2
Maths
1
3
Chemistry
1
4
Physics
1

Table: Employee

SELECT * FROM Employee  WITH (NOLOCK)

EmployeeID
DepartmentID
EmployeeName
Gender
BirthDate
Salary
Detail
1
1
Krishnan
M
4/4/1974
30000
Krishnan Details
2
2
Raman
M
12/31/1974
29000
Raman Details
3
3
Durga
F
10/2/1980
25000
Durga Details
4
3
Saratha
F
9/2/1978
26000
Saratha Details
5
2
Shivan
M
11/6/1970
45000
Shivan Details
6
3
Parvathi
F
9/18/1975
35000
Parvathi Details
7
0
Easwari
F
10/12/1990
10000
Parvathi Details
8
0
Donald
M
11/11/1990
12000
Donald Details



By looking the above sample table data, we can say

The EmployeeID 7 and 8 do not have the DepartmentID (not associated with department).
The DepartmentID 4 does not have Employees.

_______________________________________________

LEFT JOIN: Select all records from the LEFT table and matched records from RIGHT table.




-- EXAMPLE QUERY FOR LEFT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
LEFT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID


RESULT: Returns all the Employee(LEFT) table records, including not matched with department(RIGHT) table.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
5
Shivan
2
Maths
Male
45000
6
Parvathi
3
Chemistry
Female
35000
7
Easwari
NULL
NULL
Female
10000
8
Donald
NULL
NULL
Male
12000

_______________________________________________

RIGHT JOIN: Select all records from the RIGHT table and matched records from LEFT table.


-- EXAMPLE QUERY FOR RIGHT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName,   
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
RIGHT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID

RESULT: Returns all the Department(RIGHT) table records, including not matched with Employee(LEFT) table.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
5
Shivan
2
Maths
Male
45000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
6
Parvathi
3
Chemistry
Female
35000
NULL
NULL
4
Physics
NULL
NULL