MySQL UNION Operator

Last updated: October 19, 2026Oct 19, 2026

UNION combines the results of two or more SELECT statements into one result, placing rows one under another and removing duplicates. Use it when similar data lives in different tables: customers and suppliers, this year's and last year's orders, active and archived records.

1. How UNION Works

SELECT columns FROM table1
UNION [DISTINCT]
SELECT columns FROM table2
[UNION SELECT …]
[ORDER BY …] [LIMIT …];

2. A Basic UNION

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Result: 4 rows

Delhi and Goa appear in both tables but only once in the result.

3. The Rules

• Every SELECT must return the same number of columns.
• Columns are matched by position, not by name; their types should be compatible.
• Result column names come from the first SELECT.
• Duplicates are judged on whole rows.

SELECT name, city FROM customers
UNION
SELECT company FROM suppliers;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

SELECT name AS contact, city FROM customers
UNION
SELECT company, city FROM suppliers;

Result: 6 rows

4. Labelling Where Rows Come From

SELECT 'customer' AS type, name AS contact, email FROM customers
UNION
SELECT 'supplier', company, email FROM suppliers
ORDER BY type, contact;

Result: 6 rows

A mailing list from both tables, with a column saying which table each row came from.

5. ORDER BY and LIMIT

One ORDER BY at the end sorts the whole combined result. To sort or limit an individual SELECT, wrap it in parentheses:

(SELECT order_id, amount FROM orders_2025 ORDER BY amount DESC LIMIT 1)
UNION
(SELECT order_id, amount FROM orders_2026 ORDER BY amount DESC LIMIT 1)
ORDER BY amount DESC;

Result: 2 rows

The biggest order of each year.

6. Duplicates Are Removed, Sometimes Surprisingly

Customer 1 placed a ₹500 order in both years. Selecting only customer and amount makes the two rows identical, so UNION keeps one:

SELECT customer_id, amount FROM orders_2025
UNION
SELECT customer_id, amount FROM orders_2026
ORDER BY customer_id;

Result: 5 rows

SELECT SUM(amount) AS total_wrong FROM (
  SELECT customer_id, amount FROM orders_2025
  UNION
  SELECT customer_id, amount FROM orders_2026
) t;

Result: 1 row

7. INTERSECT and EXCEPT (MySQL 8.0.31+)

Newer MySQL versions add the other set operations. Cities with both customers and suppliers, and cities with customers only:

SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

INTERSECT: in both

SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

EXCEPT: in the first only

8. UNION vs JOIN

9. Practice Exercises

SELECT email FROM customers
UNION
SELECT email FROM suppliers
ORDER BY email;

SELECT 2025 AS yr, order_id, amount FROM orders_2025 WHERE amount > 600
UNION
SELECT 2026, order_id, amount FROM orders_2026 WHERE amount > 600
ORDER BY amount DESC;

10. Summary

• UNION stacks the results of several SELECTs and removes duplicate rows.
• Same number of columns, matched by position; names come from the first SELECT.
• One ORDER BY/LIMIT at the end; use parentheses for per-SELECT ordering.
• Use UNION ALL to keep duplicates (and for speed); INTERSECT/EXCEPT exist since 8.0.31.