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?
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;
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;
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;
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;