SQL Outer Join
An OUTER JOIN is used to include rows from the tables in the JOIN even it criteria is not met.
There are three types of OUTER JOIN.
LEFT OUTER JOIN: This join will also include rows from the LEFT table that do not match the criteria. The corresponding rows from RIGHT table will be entered as NULL.
RIGHT OUTER JOIN: This join will also include rows from the RIGHT table that do not match the criteria. The corresponding rows from the LEFT table will be entered as NULL.
FULL OUTER JOIN: This join will include rows from both the LEFT and RIGHT table that do not match the criteria. The corresponding rows from the LEFT table will be entered as NULL. The corresponding rows from the RIGHT table will be entered as NULL.
Let us see examples of the same:
Imagine that there are two tables. Employee and Department as shown below.
Employee Table
Department Table
For the above scenario, we can join the two tables using the JOIN clause.
SELECT EMPLOYEE.FIRSTNAME, EMPLOYEE.LASTNAME, DEPARTMENT.DEPARTMENTNAME
FROM EMPLOYEE LEFT OUTER JOIN DEPARTMENT
ON EMPLOYEE.DEPARTMENTID = DEPARTMENT.DEPARTMENTIDBy doing that, But, empty value is populated for the row (Chatur, Bansal) because there is no corresponding entry in the department table.
Scenario 2 (RIGHT OUTER JOIN): Get Employee First Name, Last Name and Department Name.
For the above scenario, we can join the two tables using the JOIN clause.
SELECT EMPLOYEE.FIRSTNAME, EMPLOYEE.LASTNAME, DEPARTMENT.DEPARTMENTNAME
FROM EMPLOYEE RIGHT OUTER JOIN DEPARTMENT
ON EMPLOYEE.DEPARTMENTID = DEPARTMENT.DEPARTMENTIDBy doing that, even though there are no matching records for Administration in the Employee table, we are still showing blank for FIRSTNAME and LASTNAME.
Scenario 3 (FULL OUTER JOIN): Get Employee First Name, Last Name and Department Name.
For the above scenario, we can join the two tables using the JOIN clause.
SELECT EMPLOYEE.FIRSTNAME, EMPLOYEE.LASTNAME, DEPARTMENT.DEPARTMENTNAME
FROM EMPLOYEE FULL OUTER JOIN DEPARTMENT
ON EMPLOYEE.DEPARTMENTID = DEPARTMENT.DEPARTMENTIDBy doing that, we are successfully able to show the department name also in the result. But, empty value is populated for the row (Chatur, Bansal) because there is no corresponding entry in the department table. Since this is full outer join, we are also showing the Administration row from department with NULL values for FIRSTNAME AND LASTNAME.