'Full Join' is a type of join that displays all the records of the tables that are joined regardless of the matching data.
The Full Join
The 'Full Join 'is a type of outer join. It is a combination of the Right Outer Join and the Left Outer Join.It
returns records where conditions are met and null values where there are no matching data.
SQL 'Full Outer Join' Syntax
Select tableA.column1,tableA.column-2,tableA.column-3,...tableA.column-n,tableB.column1,
tableB.column2,.....tableB.columnn
from table A
FULL JOIN tableB on tableA.columnname= tableB.columnname
The Full Join Example
The 'Employees' tables contains the following columns and their respective data as shown
below.
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
950.00 |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
800.00 |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
700.00 |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
600.00 |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
500.00 |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
400.00 |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
650.00 |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
600.00 |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
750.00 |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
450.00 |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
500.00 |
The
'Department' table contains the following columns and their respective data as shown
below.
1 |
Finance |
2 |
Development |
3 |
Testing |
4 |
Delivery |
5 |
Human Resources |
6 |
Administration |
Select A.EmployeeID, A.EmployeeName, A.DateOfBirth, A.DesignationID, A.City, A.Salary ,
B.DeptID, B.DeptName
from Employees A
Full Join Department B on A.DeptID=B.DeptID
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
New Orleans |
950.00 |
1 |
Finance |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
San Francisco |
800.00 |
2 |
Development |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
New York |
700.00 |
4 |
Delivery |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
Salt Lake City |
600.00 |
4 |
Delivery |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
New York |
500.00 |
3 |
Testing |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
Houston |
400.00 |
2 |
Development |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
Long Island |
650.00 |
6 |
Administration |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
Salt Lake City |
600.00 |
2 |
Development |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
San Francisco |
750.00 |
3 |
Testing |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
Salt Lake City |
450.00 |
2 |
Development |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
Austin |
500.00 |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
5 |
Human Resources |
Note: The Full Join occurs on the common column
DeptID which is present in both the tables. The unmatched records in both the tables are displayed in conjunction with their null values.