UNION Operator

Overview

The UNION operator combines the result sets of two or more SELECT queries into a single result set, automatically removing duplicate rows.

Learning Objectives

• Combine query outputs vertically using UNION.
• Understand structural constraints: matching column counts and compatible datatypes.
• Analyze the performance overhead of UNION deduplication sorting.

Detailed Concept Explanation

UNION stacks query result sets vertically (combining rows, whereas JOIN combines columns side-by-side).

Rules for Set Operators:

1. Each SELECT statement must return the same number of columns.
2. Corresponding columns must have compatible data types.
3. Column names in the final output are inherited from the first SELECT query.

Because UNION automatically eliminates duplicate rows across query sets, the database engine executes a memory-intensive deduplication sort operation under the hood.

Code Examples

SQL

-- Combine client and partner contacts into a unique list
SELECT name, email, 'Client' AS source FROM clients
UNION
SELECT name, email, 'Partner' AS source FROM partners;

Best Practices

• Use UNION ALL when Duplicates are Impossible: If you know the two datasets do not overlap (or duplicate removal is unnecessary), use UNION ALL instead to avoid the expensive deduplication sort step.

Interview Perspective

Interactive Challenges

Summary

UNION stacks query rows vertically while automatically deduplicating rows.