SQL FULL OUTER JOIN & UNION
Chapter 24 of 40 · Estimated read time: 8 min
FULL OUTER JOIN
FULL OUTER JOIN returns all rows from both tables. Where there is a match, the columns from both tables are filled in. Where there is no match on either side, the missing columns are NULL. It is the union of LEFT JOIN and RIGHT JOIN.
ALL
LEFT
LEFT
ALL
RIGHT
RIGHT
Every row from both tables — NULLs fill the gaps where there is no match.
SELECT t1.col, t2.col
FROM table1 AS t1
FULL OUTER JOIN table2 AS t2 ON t1.key = t2.key;
MySQL does not support FULL OUTER JOIN. PostgreSQL, SQL Server, and Oracle do. In MySQL, simulate it by combining LEFT JOIN and RIGHT JOIN with UNION (shown below).
Example 1 — All Customers and All Products Side by Side
Imagine checking which customers have orders and which products have orders — all unmatched rows appear with NULLs:
-- PostgreSQL / SQL Server / Oracle SELECT c.customer_name, p.product_name, o.order_id FROM customers AS c FULL OUTER JOIN orders AS o ON c.customer_id = o.customer_id FULL OUTER JOIN products AS p ON p.product_id = o.product_id ORDER BY o.order_id;
Customers with no orders and products with no orders both appear
| customer_name | product_name | order_id |
|---|---|---|
| Amit Verma | Wireless Mouse | 1001 |
| Priya Sharma | USB-C Hub | 1002 |
| Amit Verma | Notebook A5 | 1003 |
| Rohan Mehta | Ergonomic Chair | 1004 |
| Sneha Gupta | Desk Lamp | 1005 |
| Arjun Singh | HDMI Cable | 1006 |
| Amit Verma | Ballpoint Pens | 1007 |
| Kavita Rao | NULL | NULL |
| NULL | Standing Desk | NULL |
Simulating FULL OUTER JOIN in MySQL
-- MySQL workaround: LEFT JOIN UNION RIGHT JOIN SELECT c.customer_name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id UNION SELECT c.customer_name, o.order_id FROM customers AS c RIGHT JOIN orders AS o ON c.customer_id = o.customer_id; -- UNION automatically removes duplicate rows from the overlap
UNION deduplicates. If you want all rows including duplicates, use UNION ALL — which brings us to the next topic.
UNION and UNION ALL
UNION stacks the results of two SELECT statements on top of each other — vertically, not horizontally like a JOIN. Both queries must return the same number of columns in the same order with compatible types.
UNION
- Combines two result sets
- Removes duplicate rows
- Slower (sorts to deduplicate)
UNION ALL
- Combines two result sets
- Keeps ALL rows including duplicates
- Faster (no deduplication step)
SELECT col1, col2 FROM table1
UNION -- or UNION ALL
SELECT col1, col2 FROM table2;
-- Column count, order, and types must match
Example 2 — Combine Two City Lists
-- All unique cities from customers AND orders (if orders had a city column)
-- Using our dataset: cities from customers + cities from a second customer list
SELECT city FROM customers
UNION
SELECT city FROM customers WHERE city IN ('Pune', 'Chennai');
-- UNION removes duplicates — each city appears only onceUnique cities across both queries
| city |
|---|
| Noida |
| Delhi |
| Mumbai |
| Bengaluru |
| Pune |
| Chennai |
Example 3 — Combine Order Records Across Two Periods
-- Stack October and November orders into one result -- (imagine november_orders is a separate table) SELECT order_id, customer_id, total_amount, 'October' AS month FROM orders WHERE order_date BETWEEN '2026-10-01' AND '2026-10-31' UNION ALL SELECT order_id, customer_id, total_amount, 'November' AS month FROM orders WHERE order_date BETWEEN '2026-11-01' AND '2026-11-30'; -- UNION ALL keeps all rows — no deduplication
Column name in the output comes from the first SELECT. Aliases in the second SELECT are ignored — only the first query's column names matter.
JOIN vs UNION — Key Difference
| JOIN | UNION | |
|---|---|---|
| Direction | Horizontal — adds columns | Vertical — adds rows |
| Purpose | Combine related columns from multiple tables | Stack rows from multiple queries |
| Column requirement | Tables linked by a shared key column | Both queries must have same column count & types |
| Duplicates | Not automatically removed | UNION removes; UNION ALL keeps |
✏️ Practice Exercises
1. Simulate FULL OUTER JOIN in MySQL between customers and orders using LEFT JOIN UNION RIGHT JOIN.
2. Use UNION to list all distinct product categories from the products table combined with a hardcoded list: ('Services', 'Digital').
3. What is the difference between UNION and UNION ALL? When would you prefer each?
Show Answers
-- 1.
SELECT c.customer_name, o.order_id
FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_name, o.order_id
FROM customers AS c RIGHT JOIN orders AS o ON c.customer_id = o.customer_id;
-- 2.
SELECT DISTINCT category FROM products
UNION
SELECT category FROM (VALUES ('Services'), ('Digital')) AS extra(category);
-- 3.
-- UNION removes duplicates (use when combining lists that may overlap,
-- e.g. merging two city tables where cities repeat).
-- UNION ALL keeps all rows including duplicates — faster because no
-- sort/dedup step. Use when you know rows won't duplicate or you
-- want to count total rows (e.g. appending monthly logs).