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)
Table:
Employee
SELECT * FROM
Employee WITH
(NOLOCK)
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.
_______________________________________________
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
