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.

Practice

1. What does the UNION operation do when combining results from two SELECT queries?

easy

A. Sorts the results in ascending order

B. Combines results and removes duplicate rows

C. Combines results and keeps all duplicate rows

D. Joins tables based on a common column

Solution

1. Step 1: Understand UNION operation

   The UNION operation combines results from two SELECT queries into one result set.

2. Step 2: Check duplicate handling

   UNION removes duplicate rows, unlike UNION ALL which keeps duplicates.

3. Final Answer:

   Combines results and removes duplicate rows -> Option B

4. Quick Check:

   UNION removes duplicates = C [OK]

Hint: UNION removes duplicates; UNION ALL keeps them [OK]

Common Mistakes:

• Confusing UNION with UNION ALL
• Thinking UNION sorts results automatically
• Mixing UNION with JOIN operations

2. Which of the following is the correct syntax to combine two SELECT queries with UNION and sort the final result by column name?

easy

A. SELECT name FROM table1 UNION ORDER BY name SELECT name FROM table2;

B. SELECT name FROM table1 ORDER BY name UNION SELECT name FROM table2;

C. SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name;

D. SELECT name FROM table1 UNION SELECT name FROM table2 SORT BY name;

Solution

1. Step 1: Understand correct UNION syntax

   The UNION combines two SELECT queries; ORDER BY applies after the last SELECT.

2. Step 2: Check placement of ORDER BY

   ORDER BY must come after the entire UNION, not between or before queries.

3. Final Answer:

   SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name; -> Option C

4. Quick Check:

   ORDER BY after UNION = A [OK]

Hint: Place ORDER BY after all UNION queries [OK]

Common Mistakes:

• Putting ORDER BY before UNION
• Using SORT BY instead of ORDER BY
• Placing ORDER BY between SELECTs

3. Given two tables:
table1 with values (1, 'Alice'), (2, 'Bob')
table2 with values (2, 'Bob'), (3, 'Charlie')
What is the result of this query?

SELECT id, name FROM table1 UNION SELECT id, name FROM table2 ORDER BY id;

medium

A. (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')

B. (1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Charlie')

C. (2, 'Bob'), (3, 'Charlie')

D. (1, 'Alice'), (3, 'Charlie')

Solution

1. Step 1: Combine rows with UNION

   UNION merges rows from both tables and removes duplicates, so (2, 'Bob') appears once.

2. Step 2: Sort combined results by id

   Ordering by id gives rows in order: 1, 2, 3.

3. Final Answer:

   (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') -> Option A

4. Quick Check:

   UNION removes duplicates, ORDER BY sorts = B [OK]

Hint: UNION removes duplicates; ORDER BY sorts final list [OK]

Common Mistakes:

• Expecting duplicates with UNION
• Ignoring ORDER BY sorting
• Confusing UNION with UNION ALL

4. Identify the error in this SQL query:

SELECT name FROM table1 UNION ALL ORDER BY name SELECT name FROM table2;

medium

A. SELECT statements must have different column names

B. UNION ALL cannot be used with ORDER BY

C. Missing semicolon after first SELECT

D. ORDER BY is placed incorrectly between UNION ALL and second SELECT

Solution

1. Step 1: Check UNION ALL syntax

   UNION ALL combines two SELECT queries; ORDER BY must come after both queries, not between.

2. Step 2: Identify ORDER BY placement error

   ORDER BY is incorrectly placed between UNION ALL and second SELECT, causing syntax error.

3. Final Answer:

   ORDER BY is placed incorrectly between UNION ALL and second SELECT -> Option D

4. Quick Check:

   ORDER BY after UNION ALL queries = D [OK]

Hint: ORDER BY must follow all UNION ALL queries [OK]

Common Mistakes:

• Placing ORDER BY between UNION ALL and second SELECT
• Thinking UNION ALL disallows ORDER BY
• Assuming column names must differ

5. You have two tables:
employees with columns (id, name, department)
contractors with columns (id, name, department)
Write a query to list all unique names from both tables sorted alphabetically, but include duplicates if the same name appears in both tables.
Which query achieves this?

hard

A. SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name;

B. SELECT name FROM employees UNION SELECT name FROM contractors ORDER BY name;

C. SELECT name FROM employees INTERSECT SELECT name FROM contractors ORDER BY name;

D. SELECT name FROM employees EXCEPT SELECT name FROM contractors ORDER BY name;

Solution

1. Step 1: Understand requirement for duplicates

   The query should include duplicates if the same name appears in both tables, so duplicates must be kept.

2. Step 2: Choose correct set operation

   UNION removes duplicates, so it is not suitable. UNION ALL keeps duplicates from both tables.

3. Step 3: Confirm sorting

   ORDER BY name sorts the combined result alphabetically.

4. Final Answer:

   SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name; -> Option A

5. Quick Check:

   UNION ALL keeps duplicates, ORDER BY sorts = A [OK]

Hint: Use UNION ALL to keep duplicates, ORDER BY to sort [OK]

Common Mistakes:

• Using UNION which removes duplicates
• Confusing INTERSECT or EXCEPT with UNION
• Placing ORDER BY incorrectly