Stacking results
Joins add columns by matching rows from different tables. UNION does something different: it appends the rows of one query to the rows of another. Use it when similar data lives in different places, such as customers and employees who are both "people", or when you want to merge the results of two conditions into one list.
SELECT first_name AS name, 'customer' AS type FROM customers WHERE country = 'India'
UNION
SELECT name, 'employee' FROM employees WHERE department_id = 2;+-------------+----------+
| name | type |
+-------------+----------+
| Asha | customer |
| Farid | customer |
| Kavya | customer |
| Tom Hughes | employee |
| Quinn Baker | employee |
| Rina Das | employee |
+-------------+----------+The rule is simple: every SELECT must return the same number of columns, in the same order, with compatible types. The column names in the result come from the first query, which is why the first select list contains the aliases.
UNION versus UNION ALL
UNION removes duplicate rows from the combined result. UNION ALL keeps every row. Compare them by listing the countries of customers, twice:
SELECT country FROM customers WHERE country = 'India'
UNION
SELECT country FROM customers WHERE country = 'India';+---------+
| country |
+---------+
| India |
+---------+SELECT country FROM customers WHERE country = 'India'
UNION ALL
SELECT country FROM customers WHERE country = 'India';+---------+
| country |
+---------+
| India |
| India |
| India |
| India |
| India |
| India |
+---------+The first returns one row. The second returns six: three customers from each query. Note that UNION also removes duplicates within a single query's results.
Removing duplicates means MySQL must sort or hash the whole result, which costs time and memory. If you know the queries cannot overlap, or duplicates are fine, UNION ALL is faster. Use UNION only when you need unique rows.
Sorting and limiting the result
An ORDER BY or LIMIT after the last query applies to the whole combined result. In ORDER BY you refer to the column names of the first query:
SELECT name AS item, price AS amount FROM products WHERE price > 100
UNION ALL
SELECT name, salary FROM employees WHERE salary > 120000
ORDER BY amount DESC;+-----------------------------+-----------+
| item | amount |
+-----------------------------+-----------+
| Meera Iyer | 150000.00 |
| Nina Petrov | 125000.00 |
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+-----------+To sort or limit one part first, put that query in parentheses:
(SELECT name, price FROM products ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT name, price FROM products ORDER BY price ASC LIMIT 2);+-----------------------------+--------+
| name | price |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
| Mechanical Keyboard | 89.50 |
| Water Bottle | 12.00 |
| Resistance Bands | 14.99 |
+-----------------------------+--------+This returns the two most expensive and the two cheapest products in one list.
Adding a label column
Because the origin of each row would otherwise be lost, it is common to add a constant column, as in 'customer' AS type above. A frequent use is a combined activity feed:
SELECT signup_date AS happened_on, CONCAT(first_name, ' joined') AS event FROM customers WHERE id <= 2
UNION ALL
SELECT order_date, CONCAT('order #', id, ' was ', status) FROM orders WHERE id <= 2
ORDER BY happened_on, event;+-------------+------------------------+
| happened_on | event |
+-------------+------------------------+
| 2023-01-15 | Asha joined |
| 2023-02-03 | Ben joined |
| 2024-01-05 | order #1 was delivered |
| 2024-02-14 | order #2 was delivered |
+-------------+------------------------+Emulating a full outer join
FULL OUTER JOIN is not available in MySQL. A left join plus a right join, combined with UNION, gives the same rows. This example uses departments and employees; Finance has no employees and Victor has no department:
SELECT d.name AS department, e.name AS employee
FROM departments d LEFT JOIN employees e ON e.department_id = d.id
UNION
SELECT d.name, e.name
FROM departments d RIGHT JOIN employees e ON e.department_id = d.id
ORDER BY department, employee;+-------------+-------------+
| department | employee |
+-------------+-------------+
| NULL | Victor Lang |
| Engineering | Meera Iyer |
| Engineering | Nina Petrov |
| Engineering | Omar Haddad |
| Engineering | Paula Gomez |
| Engineering | Sam Wright |
| Engineering | Uma Reddy |
| Finance | NULL |
| HR | Tara Singh |
| Sales | Quinn Baker |
| Sales | Rina Das |
| Sales | Tom Hughes |
+-------------+-------------+UNION (not UNION ALL) is right here, because the matched rows appear in both halves and must be de-duplicated. The result includes a row for Finance with a NULL employee, and a row for Victor with a NULL department.
INTERSECT and EXCEPT
MySQL 8.0.31 and later also support INTERSECT (rows in both results) and EXCEPT (rows in the first but not the second). They follow the same column rules:
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;+----+
| id |
+----+
| 9 |
| 12 |
+----+This returns the ids of customers with no orders. For older servers, a LEFT JOIN ... IS NULL or NOT EXISTS query gives the same answer.
Common mistakes
- Different column counts, giving "The used SELECT statements have a different number of columns".
- Expecting duplicates to remain with plain
UNION. - Using
UNIONwhenUNION ALLis enough, wasting time on de-duplication. - Putting
ORDER BYin the first query without parentheses and expecting it to sort the final result. - Mixing incompatible types in the same column position, such as a name and a number.
Exercise
Write a query that lists, in one result, the names of all products with stock below 30 (labelled low stock) and all products priced above 150 (labelled premium), keeping a product in the list twice if it is in both. Then rewrite it so a product that is in both appears once.
Show one possible answer
SELECT name, 'low stock' AS reason FROM products WHERE stock < 30
UNION ALL
SELECT name, 'premium' FROM products WHERE price > 150;
SELECT DISTINCT name FROM (
SELECT name FROM products WHERE stock < 30
UNION ALL
SELECT name FROM products WHERE price > 150
) AS flagged;+-----------------------------+-----------+
| name | reason |
+-----------------------------+-----------+
| Noise-Cancelling Headphones | low stock |
| Puzzle 1000 Pieces | low stock |
| Noise-Cancelling Headphones | premium |
+-----------------------------+-----------+
+-----------------------------+
| name |
+-----------------------------+
| Noise-Cancelling Headphones |
| Puzzle 1000 Pieces |
+-----------------------------+The headphones are both low in stock (25) and expensive, so the first query lists them twice and the second once.
Key points
- A join puts tables side by side; UNION stacks the rows of queries on top of each other.
- Each SELECT must return the same number of columns with compatible types. Column names come from the first SELECT.
- UNION removes duplicate rows; UNION ALL keeps them and is faster. Prefer UNION ALL unless you need de-duplication.
- ORDER BY and LIMIT after the last SELECT apply to the whole combined result.