SQL Reference ยท Statements and Clauses
SQL UNION and UNION ALL
Reference for SQL UNION and UNION ALL: stack the results of several queries, remove or keep duplicates, column rules, ORDER BY and dialect notes.
UNION stacks the rows of two or more queries into one result. The queries must return the same number of columns with compatible types. UNION removes duplicate rows from the combined result; UNION ALL keeps every row and is faster because it skips the duplicate check.
Think of it as appending tables, not joining them: a join adds columns side by side, a union adds rows below.
SELECT city FROM customers UNION SELECT city FROM suppliers;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
select_statement UNION [ ALL ] select_statement [ UNION [ ALL ] select_statement ... ] [ ORDER BY ... ] [ LIMIT ... ];
| Part | Description |
|---|---|
UNION |
Combines results and removes duplicate rows. |
UNION ALL |
Combines results and keeps duplicates. |
| Column rules | Same number of columns; types must be compatible. Positions matter, names do not. |
| Result names | Taken from the first query. |
ORDER BY / LIMIT |
Written once, at the end; they apply to the combined result. |
Returns: all rows of all queries, with duplicates removed for plain UNION.
Dialect differences
- Precedence: in the standard,
INTERSECTbinds tighter thanUNIONandEXCEPT. SQLite evaluates compound queries strictly left to right, which gives different results for mixed operators. Add parentheses in dialects that allow them, or use subqueries in SQLite. - Parentheses around a member query are allowed in PostgreSQL, MySQL, SQL Server and Oracle, but not in SQLite.
ORDER BYin a member needs parentheses (and usuallyLIMIT) in PostgreSQL and MySQL. SQLite does not allow it.- Oracle names the difference operator
MINUSas well asEXCEPT(21c+); seeEXCEPT. - MySQL supports
UNION [ALL | DISTINCT];DISTINCTis the default.
-- PostgreSQL / MySQL: sort inside one member (SELECT name FROM products ORDER BY price DESC LIMIT 3) UNION ALL (SELECT name FROM products ORDER BY price LIMIT 3);
Usage notes
- Prefer
UNION ALLwhen duplicates are impossible or acceptable. PlainUNIONmust sort or hash the whole result. - Duplicates are compared across all columns and NULLs count as equal, as in
SELECT DISTINCT. - Type mismatches: a text column unioned with a number may be coerced or rejected depending on the dialect.
CASTexplicitly. - Add a label column (
'cheap' AS band) to tell the sources apart. - Sorting can only reference result column names or positions, not columns of the member tables.
- Many unions of the same table with different filters are often better written as one query with
CASEorOR.
Related
INTERSECT,EXCEPT,SELECT DISTINCT,VALUES- Lessons: Set operations, Subqueries