FULL (OUTER) JOIN

A FULL OUTER JOIN in SQL is a type of join that returns all the rows from both tables, and matches rows from both sides where available. If there is no match, the result is NULL on either side. Here is the Venn diagram representation of the FULL (OUTER) JOIN:

The syntax for a FULL OUTER JOIN is as follows:

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

The query for a FULL (OUTER) JOIN and its Venn diagram is shown below (where the intersection is not returned):

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
WHERE table1.key IS NULL OR table2.key IS NULL

Let’s consider the same two tables, Orders and Customers:

Orders

OrderID  | CustomerID  | Product    | Quantity
-------- | ----------- | ---------- | --------
1        | 3           | Book       | 2
2        | 1           | Pen        | 3
3        | 2           | Notebook   | 1
4        | 5           | Pencil     | 4
5        | 1           | Eraser     | 1

Customers

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve
4          | Michael

Example Test Question

Prepare a list that includes all orders and all customers. If a customer hasn’t made an order, the order fields should be displayed as NULL. If an order doesn’t have a matching customer, the customer fields should be displayed as NULL.

Answer

To solve this, we need to do a FULL OUTER JOIN on the Customers and Orders tables. We would join them based on the CustomerID which is common in both tables.

Here is the SQL query to achieve this:

SELECT Customers.CustomerName, Orders.OrderID, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

This would return the following result:

CustomerName  | OrderID  | Product  | Quantity
------------- | -------- | -------- | --------
John          | 2        | Pen      | 3
John          | 5        | Eraser   | 1
Maria         | 3        | Notebook | 1
Steve         | 1        | Book     | 2
Michael       | NULL     | NULL     | NULL
NULL          | 4        | Pencil   | 4

In the result, you can see that all orders and all customers are included. Where there is no corresponding order for a customer (in this case, ‘Michael’), the order fields are displayed as NULL. Similarly, where there is no corresponding customer for an order (in this case, OrderID 4), the customer fields are displayed as NULL.

This is how a FULL OUTER JOIN works in SQL, providing a result set that includes all records from both tables. If there is no match, the result is NULL on the corresponding side.