UNION and UNION ALL
UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates.
Overview
UNION and UNION ALL are set operations that stack the results of two or more SELECT queries on top of each other, producing a single combined result set. Both queries must return the same number of columns with compatible data types; column names in the output come from the first query.
Copy code
SELECT product_id, 'online' AS channel FROM online_sales
UNION ALL
SELECT product_id, 'retail' AS channel FROM retail_sales;
UNION vs UNION ALL
UNION performs duplicate elimination across the combined rows -- every row in the output is unique, similar to wrapping the result in SELECT DISTINCT. UNION ALL keeps every row from both inputs, including duplicates, and is the better default when you know the inputs don't overlap or you actually want to preserve duplicate counts (e.g., summing revenue across sources).
Because deduplication requires comparing every output row, UNION ALL is almost always faster than UNION and should be preferred unless you specifically need duplicates removed.
DuckDB's UNION BY NAME
Standard UNION matches columns positionally -- the first column of the first query lines up with the first column of the second, regardless of name. DuckDB adds UNION BY NAME (and UNION ALL BY NAME), which instead matches columns by name, filling in NULL for columns missing from one side:
Copy code
SELECT id, name FROM table_a
UNION BY NAME
SELECT name, id, created_at FROM table_b;
This is especially useful when combining schemas that have evolved over time or come from different sources with columns in a different order.
Related set operations
UNION/UNION ALL sit alongside INTERSECT (rows common to both queries) and EXCEPT (rows in the first query but not the second) as SQL's three core set operations.
Related terms
EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.
INTERSECT →INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them.
Deduplication →Deduplication is the process of identifying and removing duplicate records from a dataset, keeping only one representative row per logical entity.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
GROUPING SETS →GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.
column →A column represents a single field or attribute in a database table or DataFrame that contains values of the same data type.
FAQS
UNION ALL is faster because it skips the duplicate-elimination step that UNION performs; use UNION ALL whenever you don't specifically need duplicates removed.
No -- standard UNION matches columns by position, not name, and the output uses the column names from the first query. DuckDB's UNION BY NAME variant matches by name instead.
Not with plain UNION -- column counts must match. With UNION BY NAME, columns present in only one query are still allowed; DuckDB fills missing values with NULL.
