UNION

Explanation

Sometimes the data you want lives in two queries — maybe customers from different cities, or a list that mixes product names and customer names. UNION stacks result sets vertically, one on top of the other, as long as they have the same number of columns.

By default UNION removes duplicate rows. If you want to keep every row (including duplicates), use UNION ALL, which is also faster because it skips the de-duplication work.

Syntax

SELECT col1, col2 FROM table_a
UNION
SELECT col1, col2 FROM table_b;

Interactive Example

Build a single "name" list that combines product names and customer names. Then compare UNION (deduplicated) with UNION ALL.

Common Mistakes

• Mismatched column counts. SELECT a FROM t1 UNION SELECT b, c FROM t2 fails because the shapes differ.
• Using UNION when you meant UNION ALL. Accidentally dropping legitimate duplicate rows can undercount results.
• Expecting column names from later queries. Only the first SELECT's names are used.

Best Practices

• Use UNION ALL unless you specifically need duplicates removed — it's cheaper.
• Add a constant label column (like kind) so you can tell which source a row came from.
• Keep the column order and types consistent across all parts of the union.

Practice Question

Write a query that returns a single column label listing every distinct category from products together with every distinct city from customers (no duplicates), ordered alphabetically.

Summary

UNION concatenates query results with matching columns, removing duplicates; UNION ALL keeps everything. Align column counts, order, and types, and remember the output names come from the first SELECT.

FAQ