Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Try it

SQL UNION versus UNION ALL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Combining different tables ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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, INTERSECT binds tighter than UNION and EXCEPT. 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 BY in a member needs parentheses (and usually LIMIT) in PostgreSQL and MySQL. SQLite does not allow it.
  • Oracle names the difference operator MINUS as well as EXCEPT (21c+); see EXCEPT.
  • MySQL supports UNION [ALL | DISTINCT]; DISTINCT is the default.
SQL
-- 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 ALL when duplicates are impossible or acceptable. Plain UNION must 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. CAST explicitly.
  • 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 CASE or OR.