UNION and UNION ALL in PostgreSQL - Step-by-Step Execution

Concept Flow - UNION and UNION ALL

Start: Two SELECT queries

↓

Execute first SELECT

↓

Combine results

↓

UNION: Remove duplicates

↓

Final result set returned

↓

End

Two SELECT queries run separately, then their results combine. UNION removes duplicates; UNION ALL keeps all rows.

Execution Sample

PostgreSQL

SELECT name FROM fruits
UNION
SELECT name FROM vegetables;

Combines names from fruits and vegetables tables, removing duplicates.

Execution Table

Step	Action	First SELECT Result	Second SELECT Result	Combined Result	Duplicates Removed?	Final Output
1	Execute first SELECT	[Apple, Banana, Orange]			N/A
2	Execute second SELECT	[Apple, Banana, Orange]	[Carrot, Banana, Tomato]		N/A
3	Combine results	[Apple, Banana, Orange]	[Carrot, Banana, Tomato]	[Apple, Banana, Orange, Carrot, Banana, Tomato]	N/A
4	Apply UNION (remove duplicates)	[Apple, Banana, Orange]	[Carrot, Banana, Tomato]	[Apple, Banana, Orange, Carrot, Tomato]	Yes	[Apple, Banana, Orange, Carrot, Tomato]
5	Return final result (UNION)					[Apple, Banana, Orange, Carrot, Tomato]
6	Apply UNION ALL (keep duplicates) [alternative after step 3]			[Apple, Banana, Orange, Carrot, Banana, Tomato]	No	[Apple, Banana, Orange, Carrot, Banana, Tomato]

💡 Execution stops after combining and applying UNION or UNION ALL rules to produce final output.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4 (UNION)	After Step 6 (UNION ALL)
First SELECT Result	empty	[Apple, Banana, Orange]	[Apple, Banana, Orange]	[Apple, Banana, Orange]	[Apple, Banana, Orange]	[Apple, Banana, Orange]
Second SELECT Result	empty	empty	[Carrot, Banana, Tomato]	[Carrot, Banana, Tomato]	[Carrot, Banana, Tomato]	[Carrot, Banana, Tomato]
Combined Result	empty	empty	empty	[Apple, Banana, Orange, Carrot, Banana, Tomato]	[Apple, Banana, Orange, Carrot, Tomato]	[Apple, Banana, Orange, Carrot, Banana, Tomato]
Final Output	empty	empty	empty	empty	[Apple, Banana, Orange, Carrot, Tomato]	[Apple, Banana, Orange, Carrot, Banana, Tomato]

Key Moments - 3 Insights

Why does UNION remove some rows but UNION ALL does not?

UNION removes duplicate rows after combining both SELECT results (see step 4 in execution_table), while UNION ALL keeps all rows including duplicates (see step 6).

Are the two SELECT queries executed separately or together?

They are executed separately first (steps 1 and 2), then their results are combined (step 3).

Does the order of rows stay the same after UNION?

No, UNION may reorder rows because it removes duplicates and returns a distinct set (step 4). UNION ALL preserves all rows in combined order (step 6).

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what happens to duplicates?

A Duplicates are removed

B Duplicates are kept

C Duplicates are doubled

D Duplicates are ignored only in first SELECT

💡 Hint

Check the 'Duplicates Removed?' and 'Final Output' columns at step 4 in execution_table.

At which step do the two SELECT query results combine?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look for the step where 'Combined Result' column first shows both SELECT results together.

If we use UNION ALL instead of UNION, what changes in the final output?

A Duplicates are removed

B Duplicates remain in the output

C Only first SELECT results are shown

D Output is empty

💡 Hint

Compare 'Final Output' at step 4 (UNION) and step 6 (UNION ALL) in execution_table.

Concept Snapshot

UNION combines results of two SELECT queries and removes duplicates.
UNION ALL combines results and keeps all duplicates.
Both require same number of columns and compatible types.
UNION sorts and removes duplicates; UNION ALL is faster and keeps all rows.
Use UNION when duplicates matter; UNION ALL for full combined data.

Full Transcript

This visual execution shows how UNION and UNION ALL work in PostgreSQL. First, two SELECT queries run separately to get their results. Then, these results combine into one list. UNION removes duplicate rows from this combined list before returning it, while UNION ALL keeps all rows including duplicates. The execution table traces each step: running each SELECT, combining results, removing duplicates for UNION, and returning the final output. Variables track the state of results after each step. Key moments clarify why duplicates are removed only by UNION and how the queries execute separately before combining. The quiz tests understanding of when duplicates are removed and when results combine. The snapshot summarizes the main differences and usage tips for UNION and UNION ALL.