beginner

UNION Operator

By DataPlayArena Technical Review BoardUpdated 2026-07-235 min readRuntime Verified

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

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

Note

Interview Question: What is the main difference between UNION and UNION ALL?

UNION removes duplicate rows between query results (requiring a distinct sort operation), whereas UNION ALL retains all rows including duplicates (executing faster without sorting).


Interactive Challenges

Challenge 1: Combine Regions

Combine unique city names from 'us_offices' and 'uk_offices' using UNION.


Summary

UNION stacks query rows vertically while automatically deduplicating rows.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.