SQL Union

What UNION Does

Most SQL clauses combine data side by side, but UNION works vertically. It takes the rows produced by one SELECT statement and appends the rows produced by another, returning them as one list. This is useful when the data you need lives in more than one table but describes the same kind of thing, such as contacts spread across a customers table and a suppliers table.

For UNION to work, every SELECT involved must follow a few simple rules: each query must return the same number of columns, the columns must appear in the same order, and the matching columns should hold compatible data types. The column names in the final result are taken from the first SELECT statement.

• Each SELECT must return an equal number of columns.
• Columns are matched by position, not by name.
• Corresponding columns should have similar data types.
• UNION removes duplicate rows; UNION ALL keeps every row.

Combining Rows from Two Tables

Suppose you keep a customers table and a suppliers table, and both store a name and a city. To build one alphabetical list of every city that appears in either table, you can select the city from each and join the two queries with UNION.

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

UNION vs UNION ALL

Removing duplicates has a cost: the database has to compare rows against each other. When you know duplicates are impossible or you actually want to keep them, use UNION ALL instead. It simply concatenates the results without the extra deduplication step, which is faster on large tables.

SELECT first_name, 'Customer' AS source FROM customers
UNION ALL
SELECT first_name, 'Supplier' AS source FROM suppliers
ORDER BY first_name;

A common trick, shown above, is to add a constant column such as 'Customer' or 'Supplier' so that after the rows are merged you can still tell which table each row came from. Because columns are matched by position, that label column must appear in the same slot in both SELECT statements.