SQL Joins
FULL OUTER JOIN
Combine every row from two tables, filling gaps with NULLs where no match exists.
- Complete Coverage β preserves all rows from both the left and right tables
- Null Matching β unmatched columns appear as NULL in the result set
- Symmetric Result β equivalent to the UNION of LEFT JOIN and RIGHT JOIN Every row finds a home, matched or not.
What Is a FULL OUTER JOIN?
DfFULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both the left and right tables. When the ON condition matches, rows are combined. When no match exists, the missing side is filled with NULL values. The result is the union of a LEFT JOIN and a RIGHT JOIN.
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.total_amount
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
If your database does not support FULL OUTER JOIN directly (e.g., MySQL), emulate it using a LEFT JOIN UNION ALL RIGHT JOIN where the right side excludes matched rows.
Syntax
SELECT columns
FROM table_a
FULL [OUTER] JOIN table_b
ON table_a.column = table_b.column;
| Keyword | Purpose |
|---|---|
FULL | Includes all rows from both tables |
OUTER | Optional keyword β FULL JOIN is equivalent |
ON | Specifies the join condition (mandatory) |
How FULL OUTER JOIN Works
| Scenario | Left Row | Right Row | Result |
|---|---|---|---|
| Match found | Included | Included | Combined columns from both tables |
| No match on left | NULL | Included | Right columns only |
| No match on right | Included | NULL | Left columns only |
Basic Example
-- Show every customer and every order, including unmatched on both sides
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.order_date,
o.total_amount
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY COALESCE(c.customer_id, 0), o.order_date;
Finding Unmatched Rows
-- Customers without any orders (NULL on right side)
-- and orders without a valid customer (NULL on left side)
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.order_date
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
OR o.order_id IS NULL;
Filtering with WHERE ... IS NULL on the join columns returns only unmatched rows. This is a common technique for data reconciliation and orphan detection.
FULL OUTER JOIN vs UNION of LEFT and RIGHT
-- These two approaches produce the same result:
-- Approach 1: FULL OUTER JOIN
SELECT c.first_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Approach 2: UNION of LEFT and RIGHT JOINs
SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.first_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
| Approach | Pros | Cons |
|---|---|---|
FULL OUTER JOIN | Clean, single query | Not supported in MySQL |
LEFT UNION ALL RIGHT | Works everywhere | Slightly more verbose |
Database Support
| Database | FULL OUTER JOIN Support |
|---|---|
| PostgreSQL | Native support |
| SQL Server | Native support |
| Oracle | Native support |
| SQLite | Not supported β use UNION workaround |
| MySQL | Not supported β use UNION workaround |
-- MySQL workaround for FULL OUTER JOIN
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
FULL OUTER JOIN with Aggregation
-- Total products per category, including empty categories and orphan products
SELECT
c.category_id,
c.category_name,
COUNT(p.product_id) AS product_count
FROM products p
FULL OUTER JOIN categories c
ON p.category_id = c.category_id
GROUP BY c.category_id, c.category_name
ORDER BY product_count DESC;
Multiple FULL OUTER JOINs
-- Join across three tables, preserving all rows from each
SELECT
c.first_name,
c.last_name,
o.order_date,
p.product_name,
oi.quantity
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id
FULL OUTER JOIN order_items oi
ON o.order_id = oi.order_id
FULL OUTER JOIN products p
ON oi.product_id = p.product_id;
Chain multiple FULL OUTER JOINs when you need a complete picture across several tables. Be aware that the result set can grow significantly β always test with small datasets first.
Performance Considerations
| Factor | Recommendation |
|---|---|
| Indexes | Create indexes on all join columns |
| Result Size | FULL OUTER JOINs can produce large sets β limit with WHERE early |
| Alternatives | Consider UNION if FULL OUTER JOIN is not supported |
| Statistics | Keep table statistics up to date for the query planner |
| Execution Plan | Use EXPLAIN to verify index usage |
-- Recommended indexes for FULL OUTER JOIN performance
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_products_category_id ON products(category_id);
Practice Exercises
Exercise 1: Find all customers without orders and all orders without valid customers.
-- Solution
SELECT
c.customer_id,
c.first_name,
o.order_id,
o.order_date
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.order_id IS NULL;
Exercise 2: Build a complete inventory report showing all products and total quantity sold, including products that were never ordered.
-- Solution
SELECT
p.product_id,
p.product_name,
p.category,
SUM(COALESCE(oi.quantity, 0)) AS total_quantity_sold,
SUM(COALESCE(oi.quantity * oi.unit_price, 0)) AS total_revenue
FROM products p
FULL OUTER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC;
Exercise 3: Create a reconciliation report that shows every employee and every department, including departments with no employees and employees not yet assigned to a department.
-- Solution
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_id,
d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL OR d.department_id IS NULL;
Key Takeaways
- FULL OUTER JOIN returns all rows from both tables, with NULLs for unmatched sides.
- It is equivalent to LEFT JOIN UNION ALL RIGHT JOIN (with exclusion).
- MySQL and SQLite do not support FULL OUTER JOIN natively β use the UNION workaround.
- Use
WHERE ... IS NULLto find unmatched rows on either side. - Index join columns and review execution plans for optimal performance.