Full Join
FULL OUTER JOIN or FULL JOIN will return matched and unmatched rows from both the tables.
SYNTAX:
SELECT T1.Column1, T2.Column2 …
FROM TABLE1 AS T1
FULL JOIN TABLE2 AS T2
ON T1.COL1 = T2.COL1
Full Join
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Table: Location
LocationID LocationCode LocationName
1 L001 San Francisco
2 L002 New York
3 L003 Chicago
4 L004 Los Angeles
Example
Query :
SELECT E.EmployeeID, E.LastName, E.FirstName, L.*
FROM Employee AS E
FULL JOIN Location AS L ON E.EmployeeLocationID = L.LocationID
Output :
EmployeeID FirstName LastName LocationID LocationCode LocationName
1 George Cruz 1 L001 San Francisco
2 John Mathew 1 L001 San Francisco
3 Ethan William 2 L002 New York
4 Jacob Logan NULL NULL NULL
5 Nathan Mateo 2 L002 New York
6 Alice Charles 1 L001 San Francisco
7 John Robert 2 L002 New York
8 Sophia James NULL NULL NULL
9 Kevin Chan 1 L001 San Francisco
10 Susan Mortiz 1 L001 San Francisco
11 Martha Miller 1 L001 San Francisco
NULL NULL NULL 3 L003 Chicago
NULL NULL NULL 4 L004 Los Angeles
Description :
The above query returns all records from both Employee table and Location table. If a row in Employee table does not have matching record in Location table, then the row output will contain NULL value for each column from Location table. Similarly if a row in Location table does not have matching record in Employee table, then the row output will contain NULL value for each column from Employee table.

As you can see, since there are no matching row in Location table for EmployeeID 4 and 8, NULL value is returned for each column from Location table. Also, since there are no matching row in Employee table for LocationID 3 and 4, NULL value is returned for each column from Employee table.