SQL Full Join
SQL FULL OUTER JOIN Keyword
When there is a match in the records of the left (table1) or right (table2) tables, the FULL OUTER JOIN keyword returns all records.
Advice: FULL JOIN and FULL OUTER JOIN are interchangeable.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Be aware that a FULL OUTER JOIN may provide incredibly big result sets!
Demo Database
We’ll be using the well-known Northwind sample database in this tutorial.
A sample from the “Customers” table is shown below:
| 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 |
And a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
SQL FULL OUTER JOIN Example
All clients and all orders are selected using the SQL statement that follows:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
A selection from the result set may look like this:
| CustomerName | OrderID |
|---|---|
| Null | 10309 |
| Null | 10310 |
| Alfreds Futterkiste | Null |
| Ana Trujillo Emparedados y helados | 10308 |
| Antonio Moreno Taquería | Null |
Note: Regardless of whether the other table matches or not, the FULL OUTER JOIN keyword delivers all matching records from both tables. Therefore, such rows will also be listed if there are rows in “Customers” that do not have matching rows in “Orders,” or if there are rows in “Orders” that do not match rows in “Customers.”
