MySQL UNION ALL

Last updated: October 19, 2026Oct 19, 2026 3 min read

UNION ALL combines the results of several SELECT statements and keeps every row, including duplicates. Because it doesn't have to find and remove duplicates, it is also faster than UNION. For combining facts such as payments, orders or log lines, it is almost always the right choice.

1. UNION ALL vs UNION

SELECT … FROM table1
UNION ALL
SELECT … FROM table2;

SELECT customer, amount FROM upi_payments
UNION ALL
SELECT customer, amount FROM card_payments;

UNION ALL: 5 rows

SELECT customer, amount FROM upi_payments
UNION
SELECT customer, amount FROM card_payments;

UNION: duplicates removed

2. Totals Across Tables

Aman really paid ₹500 three times. UNION would lose two of them; UNION ALL keeps the total correct:

SELECT customer, SUM(amount) AS total_paid, COUNT(*) AS payments
FROM (
  SELECT customer, amount FROM upi_payments
  UNION ALL
  SELECT customer, amount FROM card_payments
) all_payments
GROUP BY customer
ORDER BY total_paid DESC;

Result: 3 rows

3. Keeping Track of the Source

SELECT 'UPI'  AS method, pay_id, customer, amount, paid_on FROM upi_payments
UNION ALL
SELECT 'Card', pay_id, customer, amount, paid_on FROM card_payments
ORDER BY paid_on;

Result: 5 rows

4. Merging Archive and Live Tables

Old rows are often moved to an archive table. A view with UNION ALL lets queries see both as one:

CREATE VIEW all_logs AS
SELECT id, level, msg, logged, 'archive' AS src FROM logs_archive
UNION ALL
SELECT id, level, msg, logged, 'live' FROM logs_live;

SELECT level, COUNT(*) AS entries FROM all_logs GROUP BY level ORDER BY entries DESC;

Result: 3 rows

SELECT msg, logged, src FROM all_logs WHERE level = 'ERROR' ORDER BY logged;

Result: 3 rows

5. Building Small Lists of Rows

UNION ALL of constant SELECTs is a quick way to make a small inline table, for example to report categories with zero rows:

SELECT l.level, COUNT(a.id) AS entries
FROM (SELECT 'ERROR' AS level UNION ALL SELECT 'WARN' UNION ALL SELECT 'INFO' UNION ALL SELECT 'DEBUG') l
LEFT JOIN all_logs a ON a.level = l.level
GROUP BY l.level;

Result: 4 rows

6. Why UNION ALL Is Faster

For UNION, MySQL must put all rows into a temporary table with a unique key to discard duplicates. UNION ALL can usually stream rows straight to the client without a temporary table.

EXPLAIN SELECT customer FROM upi_payments UNION SELECT customer FROM card_payments;

UNION: extra UNION RESULT step with a temporary table

EXPLAIN SELECT customer FROM upi_payments UNION ALL SELECT customer FROM card_payments;

UNION ALL: no de-duplication step

7. When to Use Which

8. Summary

• UNION ALL appends results and keeps duplicates; UNION removes them.
• Same column rules as UNION: same count, matched by position.
• Use UNION ALL for totals and for combining archive and live data.
• UNION ALL avoids the de-duplication temp table, so it's faster.