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
nameisNULL. - 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.