UNION is the SQL set operator that combines the result sets of two or more SELECT statements into a single result, placing the rows of each query one after another and removing duplicate rows from the combined output. In SQL and relational databases, it is the standard way to merge rows that come from different tables, partitions or filtering conditions when those rows share the same column structure.
Where a JOIN widens a result by adding columns from related tables, UNION lengthens a result by adding rows. Its sibling UNION ALL performs the same concatenation but keeps every duplicate, which makes it cheaper and, in most production queries, the more appropriate choice.
How UNION combines query results
UNION evaluates each component query independently and then concatenates their rows into one relation. The plain form applies set semantics: after concatenation, the engine eliminates rows that are identical across all columns, so each distinct row appears exactly once.
The syntax places the keyword between complete SELECT statements, for example SELECT city FROM customers UNION SELECT city FROM suppliers. Any number of queries can be chained, and the operator is evaluated left to right unless parentheses change the grouping. An ORDER BY clause at the very end sorts the entire combined result, not the last component query.
Three compatibility rules apply to every component query. The following table summarizes them and what happens when one is broken.
| Rule | Requirement | Consequence if violated |
|---|---|---|
| Column count | Every SELECT returns the same number of columns | Error raised before execution |
| Column order | Columns are matched by position, not by name | Silent mixing of unrelated values |
| Data types | Corresponding columns have compatible types | Implicit conversion or a type error |
The column names of the result come from the first SELECT statement. Aliases written in later queries are ignored, which is why experienced developers alias columns in the first branch only and keep later branches aligned by position.
UNION versus UNION ALL
The difference between the two forms is duplicate elimination, and it has large consequences for performance. Plain UNION must compare every row with every other row, which the optimizer implements with a sort or a hash aggregation over the whole combined result. UNION ALL simply streams the rows of each branch to the output.
| Aspect | UNION | UNION ALL |
|---|---|---|
| Duplicate rows | Removed | Kept |
| Extra processing | Sort or hash step over all rows | None beyond reading each branch |
| Memory use | Can spill to disk on large inputs | Minimal, rows are streamed |
| Row order without ORDER BY | Undefined | Undefined |
| Typical use | Distinct lists from overlapping sources | Partitioned data, logs, disjoint sets |
A frequent mistake is to use plain UNION out of habit when the branches can never overlap, for example when each branch reads a different year of an archive table. In that case the deduplication step does no useful work but can dominate execution time on millions of rows. The decisive recommendation is simple: default to UNION ALL and choose UNION only when duplicates are genuinely possible and unwanted.
Deduplication also treats NULL values as equal to each other, unlike comparison in a WHERE clause. Two rows that differ only by containing NULL in the same column are therefore considered duplicates and collapse into one.
How UNION relates to other SQL operations
UNION belongs to the family of set operators defined in the SQL standard, alongside INTERSECT and EXCEPT. It is also often confused with JOIN, because both combine data from several tables, yet they work along different axes.
| Construct | What it combines | Shape of the result |
|---|---|---|
| UNION | Rows from all queries | Same columns, more rows |
| INTERSECT | Rows present in every query | Same columns, fewer rows |
| EXCEPT | Rows of the first query missing from the second | Same columns, fewer rows |
| JOIN | Columns of matching rows | More columns, varying rows |
| OR in WHERE | Rows of one table meeting either condition | One table, filtered rows |
When both branches read the same table, a single SELECT with an OR condition is often simpler. However, UNION ALL can outperform OR when each branch can use a different index, because the optimizer then runs two narrow index lookups instead of one broad scan. Inside a CTE, UNION ALL is also the mechanism that builds recursive queries, joining the anchor member to the recursive member.
Practical patterns and configuration choices
UNION appears in a recognizable set of everyday tasks. Choosing the right variant depends on whether overlap can occur and on how the result will be consumed.
| Situation | Recommended choice | Reason |
|---|---|---|
| Merging monthly or yearly partition tables | UNION ALL | Partitions are disjoint by design |
| Building a distinct contact list from two systems | UNION | Same person may exist in both sources |
| Adding a totals row under detail rows | UNION ALL | Total row cannot duplicate a detail row |
| Combining legacy and current schemas in a view | UNION ALL with a source column | Keeps lineage visible |
| Recursive hierarchy traversal in a CTE | UNION ALL | Required by most engines for recursion |
A useful technique is to add a literal source column to each branch, such as 'web' AS channel and 'store' AS channel. This preserves where each row came from and, as a side effect, makes otherwise identical rows distinct, so the developer stays in control of deduplication.
To limit or sort individual branches, wrap each one in parentheses or a subquery. Without that, LIMIT and ORDER BY apply to the combined result, and some engines reject an ORDER BY inside a branch altogether.
Limitations and common mistakes with UNION
Most UNION defects are silent logic errors rather than syntax errors, because the engine accepts any combination of queries that satisfies the column count and type rules.
- Misaligned columns: swapping two columns of the same type in one branch produces wrong data without any warning.
- Unintended deduplication: plain UNION removes legitimate repeated rows, such as two identical order lines, which distorts sums and counts.
- Type widening surprises: combining an integer column with a text column forces conversion, and the resulting type follows engine-specific precedence rules.
- Assumed ordering: rows do not reliably appear in branch order, so only an explicit final ORDER BY guarantees a sequence.
- Large deduplication cost: a UNION over wide rows or big inputs can trigger disk-based sorting and slow queries sharply.
Reviewing the execution plan quickly reveals whether a UNION introduces a costly distinct step. If it does and duplicates cannot occur, switching to UNION ALL is usually the single most effective fix.
In everyday language
Imagine two guest lists for the same party, one kept by each host. UNION is like copying both lists onto one sheet and crossing out any name that appears twice, so every guest is listed once.
UNION ALL does the same copying but keeps the repeats. That is faster, because nobody has to check for duplicates, and it is the better choice when the two lists cannot share any names. The only condition is that both lists use the same columns in the same order, such as name first and phone number second.
Seen in the real world
A retail analytics team stores sales in separate tables for each year, from sales_2022 to sales_2025, with about 40 million rows in total. Its monthly revenue report originally combined the tables with plain UNION, and the query took roughly nine minutes because the database sorted every row to look for duplicates.
A database engineer noticed in the execution plan that the sort step consumed most of the time and that the yearly tables could never contain the same sale. Replacing UNION with UNION ALL and adding a sale_year column to each branch removed the sort entirely. The report then finished in under one minute, and the totals stayed identical because no real duplicates had ever existed.