FULL JOIN / FULL OUTER JOIN

 

• All rows from both the left and right tables
• Matching rows are combined
• Non-matching rows from either side will have NULL in place of missing values
• There is no difference between FULL JOIN and FULL OUTER JOIN. They are simply two ways to write the same thing.

SELECT A.column1, B.column2 FROM TableA A

FULL OUTER JOIN TableB B

ON A.common_column = B.common_column;

====================================================================

emp_id	emp_name	dept_id
1	John	10
2	Alice	20
3	Bob	30

dept_id	dept_name
10	HR
20	Finance
40	Marketing

SELECT e.emp_name, d.dept_name FROM Employees e

FULL OUTER JOIN Departments d

ON e.dept_id = d.dept_id;

emp_name	dept_name
John	HR
Alice	Finance
Bob	NULL
NULL	Marketing

====================================================================

Example 2

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

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.