UNION and UNION ALL in MySQL - 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

UNION and UNION ALL combine results from two SELECT queries; UNION removes duplicates, UNION ALL keeps all rows.

Execution Sample

MySQL

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?
1	Execute first SELECT	["Apple", "Banana", "Carrot"]		["Apple", "Banana", "Carrot"]	N/A
2	Execute second SELECT		["Carrot", "Date", "Eggplant"]	["Apple", "Banana", "Carrot", "Date", "Eggplant"]	N/A
3	Combine results			["Apple", "Banana", "Carrot", "Carrot", "Date", "Eggplant"]	No
4	UNION removes duplicates			["Apple", "Banana", "Carrot", "Date", "Eggplant"]	Yes
5	UNION ALL keeps duplicates			["Apple", "Banana", "Carrot", "Carrot", "Date", "Eggplant"]	No
6	End			Final sets ready	N/A

💡 Execution stops after combining and optionally removing duplicates depending on UNION or UNION ALL.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
First SELECT Result	[]	["Apple", "Banana", "Carrot"]	["Apple", "Banana", "Carrot"]	["Apple", "Banana", "Carrot"]	["Apple", "Banana", "Carrot"]	["Apple", "Banana", "Carrot"]	["Apple", "Banana", "Carrot"]
Second SELECT Result	[]	[]	["Carrot", "Date", "Eggplant"]	["Carrot", "Date", "Eggplant"]	["Carrot", "Date", "Eggplant"]	["Carrot", "Date", "Eggplant"]	["Carrot", "Date", "Eggplant"]
Combined Result	[]	[]	[]	["Apple", "Banana", "Carrot", "Carrot", "Date", "Eggplant"]	["Apple", "Banana", "Carrot", "Date", "Eggplant"]	["Apple", "Banana", "Carrot", "Carrot", "Date", "Eggplant"]	Depends on UNION or UNION ALL

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 execution_table step 4), while UNION ALL keeps all rows including duplicates (step 5).

Are the order of rows guaranteed in UNION or UNION ALL?

No, both UNION and UNION ALL do not guarantee order. To control order, use ORDER BY after the UNION query.

Can the two SELECT queries have different columns or data types?

No, both SELECT queries must have the same number of columns and compatible data types for UNION or UNION ALL to work.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What happens to the combined result?

A Duplicates are removed

B Duplicates are added

C Rows are sorted alphabetically

D Only the first SELECT result is kept

💡 Hint

Check the 'Duplicates Removed?' column at step 4 in execution_table.

At which step does UNION ALL keep duplicates in the combined result?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Look at the 'Duplicates Removed?' column for step 5 in execution_table.

If the first SELECT returns ["Apple", "Banana"] and the second SELECT returns ["Banana", "Cherry"], what will UNION return?

A ["Apple", "Banana", "Banana", "Cherry"]

B ["Apple", "Banana", "Cherry"]

C ["Banana", "Cherry"]

D ["Apple", "Cherry"]

💡 Hint

UNION removes duplicates after combining both results, see variable_tracker and execution_table.

Concept Snapshot

UNION and UNION ALL combine results from two SELECT queries.
UNION removes duplicate rows.
UNION ALL keeps all rows including duplicates.
Both require same number of columns and compatible types.
Use ORDER BY after UNION to sort results.

Full Transcript

This visual execution shows how UNION and UNION ALL work in MySQL. First, two SELECT queries run separately. Their results are combined. UNION removes duplicate rows from the combined result, while UNION ALL keeps all rows including duplicates. The execution table tracks each step: running queries, combining results, and removing duplicates if UNION is used. The variable tracker shows how the result sets change after each step. Key moments clarify common confusions like why duplicates are removed only in UNION and not UNION ALL, and that order is not guaranteed without ORDER BY. The quiz tests understanding of these steps and outcomes. Remember, both SELECT queries must have the same number of columns and compatible data types for UNION or UNION ALL to work.