SQL FULL Join

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword retrieves all records when there is a match in either the left (table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are synonymous.

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Note: FULL OUTER JOIN has the potential to generate extensive result-sets.

Demo Database

Throughout this tutorial, we’ll be utilizing the renowned Northwind sample database.

Displayed below is a segment from the “Customers” table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

Furthermore, here’s a excerpt from the “Orders” table:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	2	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

SQL FULL OUTER JOIN Example

This SQL statement retrieves all customers and all associated orders.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

An example from the resulting set could resemble the following:

CustomerName	OrderID
Null	10309
Null	10310
Alfreds Futterkiste	Null
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	Null

Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.