FULL JOIN — Include All Records from Both Tables

A FULL JOIN returns all rows from both the left and right tables.
If there is a match between the tables, it shows the matched row.
If there is no match, the result will still include the row with NULL in the place of missing values.

Syntax

SELECT table1.column, table2.column
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Example Scenario

Using the same two tables:

customers

+-------------+--------+-------------+-----------+
| customer_id | name   | city        | country   |
+-------------+--------+-------------+-----------+
| 1           | Alice  | New York    | USA       |
| 2           | Bob    | Los Angeles | USA       |
| 3           | Emma   | London      | UK        |
| 4           | Paul   | Berlin      | Germany   |
| 5           | Olivia | Madrid      | Spain     |
+-------------+--------+-------------+-----------+

orders

+----------+-------------+--------------+--------------+
| order_id | customer_id | product_name | order_status |
+----------+-------------+--------------+--------------+
| 101      | 1           | Laptop       | Delivered    |
| 102      | 1           | Phone        | Shipped      |
| 103      | 2           | Charger      | Cancelled    |
| 104      | 3           | Watch        | Delivered    |
| 105      | 4           | Headphones   | Shipped      |
| 106      | 4           | Speakers     | Pending      |
| 107      | NULL        | Tablet       | Incomplete   |
+----------+-------------+--------------+--------------+

Let’s also assume customer Olivia (ID 5) hasn’t placed any orders.

Example Query

SELECT customers.name, orders.product_name, orders.order_status
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

This returns all customers and all orders, including:

  • Customers with no orders
  • Orders with no matching customer

Output

+--------+--------------+--------------+
| name   | product_name | order_status |
+--------+--------------+--------------+
| Alice  | Laptop       | Delivered    |
| Alice  | Phone        | Shipped      |
| Bob    | Charger      | Cancelled    |
| Emma   | Watch        | Delivered    |
| Paul   | Headphones   | Shipped      |
| Paul   | Speakers     | Pending      |
| NULL   | Tablet       | Incomplete   |
| Olivia | NULL         | NULL         |
+--------+--------------+--------------+
  • The row with NULL for name shows an order without a customer.
  • The row with NULL for product and status shows a customer with no orders.

When to Use FULL JOIN?

  • When you want everything from both tables, regardless of matches.
  • Great for identifying unmatched records in either table.
  • Helps in generating comprehensive reports with complete datasets.

Note: Full Join is not supported by all the databases. Ex. MySQL does not support full joins