FULL JOIN: Keep All Rows from Both Tables

A FULL JOIN returns all rows from both tables.
When rows match, they are joined together. When a row has no match, it is still in the result, with NULL for the missing side.


Syntax

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

Example Query

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

Output:

  name  | product_name | order_status 
--------+--------------+--------------
 Alice  | Laptop       | Delivered
 Alice  | Phone        | Shipped
 Bob    | Charger      | Cancelled
 Emma   | Watch        | Delivered
 Paul   | Headphones   | Shipped
 Paul   | Speakers     | Pending
        | Tablet       | Incomplete
 Olivia |              | 
(8 rows)

This returns all customers and all orders, including:

  • The order with no customer (Tablet): its name is NULL.
  • The customer with no orders (Olivia): her product and status are NULL.

Olivia comes last because she has no order_id. When you sort in ascending order, PostgreSQL puts NULL values last.


When to Use FULL JOIN?

  • When you want everything from both tables, matched or not.
  • To find rows without a match in either table.

Note: Not every database supports FULL JOIN. MySQL does not, but PostgreSQL does.

Answer the question below to check what FULL JOIN returns.