SQL UNION and UNION ALL: Combine Lists Without Surprises

UNION combines rows from separate query results. It does not join columns side by side. Understanding that difference helps you choose the right tool when two lists have the same shape but come from different sources.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Combine two small lists

This example builds two fictional mailing lists inside CTEs. No saved records are changed.

WITH first_list(name) AS (
  VALUES ('Amina'), ('Brian')
), second_list(name) AS (
  VALUES ('Brian'), ('Chao')
)
SELECT name FROM first_list
UNION ALL
SELECT name FROM second_list
ORDER BY name;

The result contains Amina, Brian, Brian and Chao. UNION ALL keeps every input row, including the two Brian entries. It stacks the second result below the first before the final sort.

Remove repeated result rows deliberately

WITH first_list(name) AS (
  VALUES ('Amina'), ('Brian')
), second_list(name) AS (
  VALUES ('Brian'), ('Chao')
)
SELECT name FROM first_list
UNION
SELECT name FROM second_list
ORDER BY name;

UNION returns Amina, Brian and Chao once each. Duplicate removal applies to the whole output row. If you also select a different source label, the two Brian rows will no longer be identical and both can remain.

Match the column structure

Each side must return the same number of columns. The first column on one side corresponds to the first on the other, and so on. Similar-looking headings do not automatically align fields by name. Explicitly list columns in the intended order.

Keep corresponding fields compatible in meaning and type. Combining customer_id with amount merely because both are numbers would create a technically possible but confusing list. The output should have a clear description of what one row represents.

Do not deduplicate people by name alone

Two people can share a name. UNION removes equal result rows, not confirmed duplicate identities. If your real task concerns distinct customers, use a reliable customer key and a defined merging rule. A mailing list needs verified contact details and consent rules beyond this SQL exercise.

When no duplicate removal is needed, UNION ALL expresses that intention and avoids the work of deduplication. Do not choose it solely for speed if preserving duplicates would make your report wrong.

Apply sorting to the combined result

The ORDER BY at the end sorts the complete compound result. Without it, do not rely on the order in which a tool happens to display rows. If individual branches need limits or separate ordering, use an appropriate subquery or CTE and make the intended scope clear.

Try it yourself

Combine orders below 1000 with orders of at least 1000 using UNION ALL. Return order_id and amount in both branches and sort by order_id. How many rows should there be, and why are the branches safe to combine without removing duplicates?

SELECT order_id, amount FROM orders WHERE amount < 1000
UNION ALL
SELECT order_id, amount FROM orders WHERE amount >= 1000
ORDER BY order_id;

Keep exploring: SQL learning path.