UNION & UNION ALL

Stack two query results into one — with or without deduplication

Advanced 10 minUNIONUNION ALLset operations

Merging Result Sets

UNION combines results from two SELECT statements into a single unified result set. Both queries must produce the same number of columns with compatible data types. Here's the difference between the two variants:

UNION removes duplicates — The database sorts and deduplicates the combined output from both queries. This extra processing takes time and resources, so only use it when you actually need unique rows.

UNION ALL keeps everything — No deduplication, no sorting overhead. Faster execution and explicit intent. This is what you should default to unless you specifically need duplicate rows removed from the combined results.

Column rules — Both queries must have the same number of columns with compatible types. The column names in the final result always come from the first SELECT statement, regardless of what the second query names its columns.

Knowledge Canvas

How UNION Works

Stack two result sets vertically

UNION combines + deduplicates rows from two queries
UNION ALL keeps all rows — no dedup (faster)
Both queries must have same column count + compatible types
Column names come from the FIRST SELECT

UNION vs UNION ALL

Dedup or not?

UNION: removes duplicates

UNION ALL: keeps everything

Requires sort/hash for dedup

No overhead — just append

Use when you need unique rows

Default choice — faster

UNION Rules

Column matching requirements

Same number of columns in both SELECTs
Compatible data types (number/number, text/text)
ORDER BY applies to the FINAL combined result
Column names always from the first query

UNION Gotchas

Watch out for these

UNION is slow on large sets — always prefer UNION ALL if possible
Column type mismatch → implicit casting or error
ORDER BY in individual SELECTs is ignored (use final ORDER BY)
Mixing NULLs: NULL = NULL for dedup purposes in UNION

Syntax

Syntax Template

1-- UNION — combine + remove duplicates

2SELECT

3 cols

4FROM table_a

5UNION

6SELECT

7 cols

8FROM table_b;

10-- UNION ALL — combine + keep all rows (faster)

11SELECT

12 cols

13FROM table_a

14UNION ALL

15SELECT

16 cols

17FROM table_b;

UNIONCombine + remove duplicates (slower)

UNION ALLCombine + keep all rows (faster)

Sample Data

customers

12 rows

name	country
Aarav Sharma	India
Sara Chen	Singapore
James Wilson	UK
Maria Garcia	Spain
Yuki Tanaka	Japan
Priya Patel	India
Alex Johnson	USA
Chen Wei	China
Emma Brown	Australia
Omar Hassan	UAE
Lena Muller	Germany
Ravi Kumar	India

products

10 rows

name	category
Wireless Mouse	Electronics
Office Desk	Furniture
LED Desk Lamp	Furniture
USB-C Cable	Electronics
Bluetooth Speaker	Electronics
Office Chair	Furniture
Coffee Maker	Appliances
Notebook Set	Stationery
Wireless Headphones	Electronics
Standing Desk	Furniture

Worked Example

Create a combined list of all unique countries and cities from the customers table.

SQL

1SELECT

2 country AS location,

3 'Country' AS type

4FROM customers

5UNION

6SELECT

7 city AS location,

8 'City' AS type

9FROM customers

10ORDER BY

11 location;

The first query gets all countries labeled as 'Country'. The second gets all cities labeled as 'City'. UNION combines them and removes duplicates (Singapore appears as both a country and a city, but with different type labels, so both rows survive).

Output

5 rows

location	type
Australia	Country
Bangalore	City
Berlin	City
China	Country
Delhi	City

Key Concepts

1UNION deduplicates — UNION ALL keeps all rows (faster)

2Both queries must have matching column count and types

3Column names come from the first SELECT

4Default to UNION ALL unless deduplication is needed

Pro Tip

UNION is how you merge data from different queries, time periods, or sources into a unified result.

When to Use

Combining current and archived data, merging results from different conditions, creating unified reports from separate queries.

Challenge

Solve the problem below

Create a combined list showing customer names labeled 'Customer' and product names labeled 'Product'. Use UNION ALL. Name columns: name, type.

Your Query

LEAD()

INTERSECT