Set operations with ORDER BY in SQL - Step-by-Step Execution

Concept Flow - Set operations with ORDER BY

Execute first SELECT query

↓

Execute second SELECT query

↓

Apply SET operation (UNION, INTERSECT, EXCEPT)

↓

Combine results into one result set

↓

Apply ORDER BY to combined result

↓

Return ordered final result

The database runs each SELECT, combines results using the set operation, then sorts the final combined rows with ORDER BY.

Execution Sample

SQL

SELECT name FROM students WHERE grade > 80
UNION
SELECT name FROM teachers
ORDER BY name;

This query combines student names with grade > 80 and all teacher names, then sorts all names alphabetically.

Execution Table

Step	Action	Query Part	Intermediate Result	Notes
1	Execute first SELECT	SELECT name FROM students WHERE grade > 80	['Alice', 'Bob']	Students with grade > 80
2	Execute second SELECT	SELECT name FROM teachers	['Mr. Smith', 'Ms. Jones']	All teacher names
3	Apply UNION	Combine both sets	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	Duplicates removed if any
4	Apply ORDER BY	ORDER BY name	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	Sorted alphabetically
5	Return final result	Final output	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	Query ends

💡 All steps complete, final ordered set returned

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
First SELECT result	[]	['Alice', 'Bob']	['Alice', 'Bob']	['Alice', 'Bob']	['Alice', 'Bob']	['Alice', 'Bob']
Second SELECT result	[]	[]	['Mr. Smith', 'Ms. Jones']	['Mr. Smith', 'Ms. Jones']	['Mr. Smith', 'Ms. Jones']	['Mr. Smith', 'Ms. Jones']
Combined set	[]	[]	[]	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']
Ordered result	[]	[]	[]	[]	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']	['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']

Key Moments - 2 Insights

Why does ORDER BY come after the set operation and not inside each SELECT?

ORDER BY sorts the final combined result. If used inside each SELECT, it only sorts partial results, which does not affect the final combined order (see execution_table step 4).

What happens if there are duplicate names in both SELECT queries?

UNION removes duplicates in the combined set (execution_table step 3). So duplicates appear only once in the final result.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the combined set after UNION?

A ['Alice', 'Bob']

B ['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones', 'Bob']

C ['Alice', 'Bob', 'Mr. Smith', 'Ms. Jones']

D ['Mr. Smith', 'Ms. Jones']

💡 Hint

Check the 'Intermediate Result' column at step 3 in execution_table

At which step is the final sorting applied?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look for 'Apply ORDER BY' action in execution_table

If we replaced UNION with UNION ALL, how would the combined set change at step 3?

A Duplicates would remain

B Duplicates would be removed

C Only first SELECT results would be shown

D Only second SELECT results would be shown

💡 Hint

UNION ALL keeps duplicates, unlike UNION which removes them (see variable_tracker combined set)

Concept Snapshot

Set operations combine results from multiple SELECTs.
ORDER BY sorts the final combined result.
ORDER BY must come after the set operation.
UNION removes duplicates; UNION ALL keeps duplicates.
Example: SELECT ... UNION SELECT ... ORDER BY column;

Full Transcript

This visual execution shows how SQL set operations with ORDER BY work. First, each SELECT query runs separately to get partial results. Then, the set operation like UNION combines these results into one set, removing duplicates if UNION is used. Finally, ORDER BY sorts this combined set before returning it. The execution table traces each step, showing intermediate results and how variables change. Key points include that ORDER BY applies after combining, and UNION removes duplicates while UNION ALL does not. The quiz tests understanding of these steps and effects.