➕Union

NEW

An SQL set operator that combines results of two or more SELECT statements, removing duplicates by default.

DESCRIPTION

UNION vertically concatenates compatible result sets. It aligns columns by ordinal, requiring matching data types. By default, duplicates are removed; use UNION ALL to retain duplicates for performance. It is useful for appending partitioned data or multi-source harmonization. Misaligned schemas or implicit casts can cause errors or performance issues. ORDER BY applies to the final combined set. Consider explicit column lists to avoid mistakes when upstream schemas change.

ALTERNATIVE DEFINITIONS

Microsoft Learn

Combines the results of two or more SELECT statements and returns a single result set that includes all the rows that belong to all queries in the UNION.

Specifies combination of multiple SELECT statements into one result set.

Source

PostgreSQL Documentation

UNION merges the results of two queries, eliminating duplicate rows.

Highlights duplicate elimination as default behavior.

Source

RELATED TERMS

INTERSECT EXCEPT Subquery Common Table Expression Set Operations Duplicate Elimination Vocabulary Alignment ORDER BY

COMPETING TERMS

UNION ALL Join

RELATIONS

• Narrower: Intersection over Union

KNOWLEDGE AREAS

Programming Languages Data Warehousing & BI Data Engineering

TARGET AUDIENCE

Data Engineer Data Analyst BI Specialist Software Developer Data Modeler

EXTERNAL SOURCES

• Wikipedia - Union (SQL)
• Microsoft - UNION operator reference
• PostgreSQL - UNION and UNION ALL semantics
• Oracle - UNION operator syntax

TOP QUESTIONS

When should I use UNION versus UNION ALL?

Use UNION when duplicate elimination is required; otherwise use UNION ALL for better performance when duplicates are acceptable or impossible.

Category: Alternatives

What are common mistakes with UNION?

Mismatched column counts or incompatible data types cause errors. Relying on implicit column order without explicit lists risks schema drift issues.

Category: Mistakes

Does ORDER BY apply to each SELECT in a UNION?

ORDER BY applies to the final combined result. Use subqueries if you need ordering within components.

Category: Misunderstanding

What is the performance impact of UNION?

UNION requires duplicate elimination, often via sort or hash, which can be expensive on large datasets.

Category: Performance

Can I UNION tables with different schemas?

Yes, but you must select compatible columns in the same order and cast as needed to align types.

Category: Requirements

How does UNION relate to set theory?

UNION corresponds to set union, combining elements from both sets without duplicates.

Category: Example

What alternatives exist to UNION for combining data?

JOINs combine horizontally by keys; UNION stacks vertically. Consider staging tables for large-scale appends.

Category: Alternatives

Is UNION deterministic?

The content is deterministic given inputs, but row order is not unless explicitly ordered in the final SELECT.

Category: Requirements

Where should I learn more about UNION?

Consult your database’s set operators documentation with examples and edge cases for NULL handling and type coercion.

Category: Learning