Set operations with ORDER BY in SQL - Deep Dive

Overview - Set operations with ORDER BY

What is it?

Set operations in SQL combine results from two or more queries into a single result. Common set operations include UNION, INTERSECT, and EXCEPT, which merge, find common rows, or find differences between query results. ORDER BY is used to sort the final combined result by one or more columns. When using set operations, ORDER BY applies to the entire combined output, not individual queries.

Why it matters

Without set operations, combining data from multiple queries would require complex and inefficient methods. ORDER BY after set operations ensures the final data is presented in a meaningful order, making it easier to read and analyze. Without understanding how ORDER BY works with set operations, you might get unexpected or unsorted results, causing confusion or errors in reports and applications.

Where it fits

Before learning set operations with ORDER BY, you should understand basic SQL SELECT queries, filtering with WHERE, and simple ORDER BY usage. After mastering this, you can explore advanced SQL topics like window functions, subqueries, and query optimization.

Mental Model

Core Idea

Set operations combine multiple query results into one, and ORDER BY sorts the entire combined result as a final step.

Think of it like...

Imagine you have two lists of names from different classrooms. Set operations are like putting these lists together, finding common names, or removing duplicates. ORDER BY is like arranging the final list alphabetically after combining.

┌───────────────┐   ┌───────────────┐
│   Query 1     │   │   Query 2     │
└──────┬────────┘   └──────┬────────┘
       │                   │
       └─────┬─────────────┘
             │ Set Operation (UNION, INTERSECT, EXCEPT)
             ▼
      ┌───────────────┐
      │ Combined Rows │
      └──────┬────────┘
             │
             ▼
      ┌───────────────┐
      │   ORDER BY    │
      └───────────────┘
             │
             ▼
      ┌───────────────┐
      │ Final Sorted  │
      │   Result Set  │
      └───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic Set Operations Overview

Concept: Introduce the main set operations: UNION, INTERSECT, and EXCEPT.

UNION combines rows from two queries and removes duplicates. INTERSECT returns only rows common to both queries. EXCEPT returns rows from the first query that are not in the second. Each query must have the same number of columns and compatible data types.

Result

You can combine or compare two query results in simple ways.

Understanding these basic operations is essential because they let you merge or compare data sets without complex joins.

2

Foundation

ORDER BY in Simple Queries

Concept: Learn how ORDER BY sorts query results by one or more columns.

ORDER BY sorts rows after the query runs. You can sort ascending (ASC) or descending (DESC). For example, SELECT name FROM students ORDER BY name ASC sorts names alphabetically.

Result

Query results appear sorted as requested.

Knowing how ORDER BY works in simple queries prepares you to understand its role after set operations.

3

Intermediate

ORDER BY with UNION

🤔Before reading on: Do you think ORDER BY can be applied to each query separately in a UNION, or only once after the UNION? Commit to your answer.

Concept: ORDER BY applies only once after the UNION operation, not to individual queries.

When using UNION, you cannot put ORDER BY inside each SELECT. Instead, you write ORDER BY after the last SELECT to sort the combined result. For example: SELECT name FROM class1 UNION SELECT name FROM class2 ORDER BY name ASC;

Result

The combined list of names from both classes is sorted alphabetically.

Understanding that ORDER BY sorts the final combined result prevents confusion and errors when trying to sort individual parts.

4

Intermediate

ORDER BY with INTERSECT and EXCEPT

🤔Before reading on: Does ORDER BY behave the same way with INTERSECT and EXCEPT as with UNION? Commit to your answer.

Concept: ORDER BY always sorts the final combined result after INTERSECT or EXCEPT operations.

Like UNION, INTERSECT and EXCEPT combine query results, and ORDER BY must be placed after the last query. For example: SELECT id FROM employees INTERSECT SELECT id FROM managers ORDER BY id DESC;

Result

You get the common employee IDs sorted in descending order.

Knowing ORDER BY's consistent placement across set operations helps write correct and predictable queries.

5

Intermediate

Column References in ORDER BY After Set Operations

Concept: ORDER BY refers to columns by position or name from the combined result, not from individual queries.

After set operations, the column names come from the first query. You can use column names or numbers in ORDER BY. For example: SELECT name, age FROM class1 UNION SELECT name, age FROM class2 ORDER BY 2 DESC; -- sorts by age descending

Result

The combined result is sorted by the second column (age) in descending order.

Understanding column references avoids errors and clarifies how sorting applies to combined data.

6

Advanced

Using ORDER BY Inside Subqueries with Set Operations

🤔Before reading on: Can ORDER BY inside subqueries affect the final combined result's order? Commit to your answer.

Concept: ORDER BY inside subqueries does not guarantee order in the final combined result; only the outer ORDER BY matters.

If you write ORDER BY inside each SELECT used in a set operation, it is ignored for the final output order. Only the ORDER BY after the last query controls sorting. For example: (SELECT name FROM class1 ORDER BY name DESC) UNION (SELECT name FROM class2 ORDER BY name ASC) ORDER BY name ASC;

Result

The final list is sorted ascending by name, ignoring inner ORDER BY clauses.

Knowing this prevents wasted effort and confusion about sorting behavior in complex queries.

7

Expert

Performance and Optimization with ORDER BY and Set Operations

🤔Before reading on: Do you think adding ORDER BY after set operations always slows down the query significantly? Commit to your answer.

Concept: ORDER BY after set operations can impact performance, but databases optimize sorting and set operations together when possible.

Databases may use indexes or internal sorting algorithms to optimize combined queries with ORDER BY. However, large datasets and complex set operations can still cause slowdowns. Understanding execution plans helps optimize queries. For example, using UNION ALL instead of UNION avoids duplicate removal and can speed up sorting.

Result

Queries run efficiently when optimized, but careless use of ORDER BY and set operations can cause delays.

Understanding how databases process set operations with ORDER BY helps write faster, scalable queries.

Under the Hood

Set operations work by executing each query separately, then combining their results according to the operation (UNION merges and removes duplicates, INTERSECT finds common rows, EXCEPT finds differences). After combining, the database applies ORDER BY to the entire result set as a final step. Internally, the database may use temporary storage or sorting algorithms to handle this efficiently.

Why designed this way?

This design keeps set operations consistent and predictable. Applying ORDER BY only once after combining avoids ambiguity about sorting partial results. It also allows databases to optimize sorting and combining steps together. Earlier SQL standards and implementations followed this pattern to maintain simplicity and compatibility.

┌───────────────┐   ┌───────────────┐
│   Query 1     │   │   Query 2     │
└──────┬────────┘   └──────┬────────┘
       │                   │
       └─────┬─────────────┘
             │ Set Operation
             ▼
      ┌───────────────┐
      │ Combined Rows │
      └──────┬────────┘
             │
             ▼
      ┌───────────────┐
      │   ORDER BY    │
      └──────┬────────┘
             │
             ▼
      ┌───────────────┐
      │ Final Sorted  │
      │   Result Set  │
      └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can you put ORDER BY inside each SELECT of a UNION to sort partial results? Commit to yes or no.

Common Belief: You can put ORDER BY inside each SELECT in a UNION to sort each part separately.

Reality: ORDER BY inside individual SELECTs in set operations is ignored for the final output order; only the last ORDER BY after the set operation matters.

Why it matters: Trying to sort partial results inside set operations leads to confusion and unexpected output order.

Quick: Does ORDER BY apply before or after removing duplicates in UNION? Commit to before or after.

Common Belief: ORDER BY sorts rows before duplicates are removed in UNION.

Reality: ORDER BY is applied after duplicates are removed, sorting the final combined result.

Why it matters: Misunderstanding this can cause wrong assumptions about which rows appear and their order.

Quick: Does the column name in ORDER BY come from the last query or the first query in a set operation? Commit to first or last.

Common Belief: ORDER BY column names come from the last query in the set operation.

Reality: ORDER BY column names come from the first query in the set operation.

Why it matters: Using column names from the wrong query causes errors or unexpected sorting.

Quick: Does adding ORDER BY always cause a big performance hit? Commit to yes or no.

Common Belief: ORDER BY always makes queries much slower, especially with set operations.

Reality: While ORDER BY can add overhead, databases optimize sorting and set operations together, so performance impact varies.

Why it matters: Assuming ORDER BY always slows queries may lead to premature optimization or avoiding useful sorting.

Expert Zone

1

Some databases allow ORDER BY with LIMIT inside subqueries in set operations, but the final output order still depends on the outer ORDER BY.

2

Using UNION ALL instead of UNION skips duplicate removal, which can improve performance when duplicates are not a concern.

3

Column aliases in the first query define the column names for ORDER BY, so consistent naming is crucial for clarity.

When NOT to use

Set operations with ORDER BY are not ideal when you need to sort each individual query result separately before combining. In such cases, consider using JOINs or window functions. Also, avoid using ORDER BY inside subqueries expecting it to affect final order; use outer ORDER BY instead.

Production Patterns

In production, UNION ALL with ORDER BY is common for combining large datasets efficiently. INTERSECT and EXCEPT are used for filtering data sets, often with ORDER BY to present results clearly. Developers often rely on ORDER BY after set operations to ensure consistent report ordering and user interface display.

Connections

Relational Algebra

Set operations in SQL directly implement relational algebra operations like union, intersection, and difference.

Understanding relational algebra helps grasp why SQL set operations behave as they do and their mathematical properties.

Sorting Algorithms

ORDER BY relies on sorting algorithms internally to arrange rows efficiently.

Knowing how sorting works under the hood explains performance impacts and optimization opportunities.

Set Theory (Mathematics)

SQL set operations mirror set theory concepts of union, intersection, and difference.

Recognizing this connection clarifies the logic behind combining and filtering data sets.

Common Pitfalls

#1 Trying to put ORDER BY inside each SELECT of a UNION to sort partial results.

Wrong approach:

SELECT name FROM class1 ORDER BY name ASC
UNION
SELECT name FROM class2 ORDER BY name DESC;

Correct approach:

SELECT name FROM class1
UNION
SELECT name FROM class2
ORDER BY name ASC;

Root cause:

Misunderstanding that ORDER BY inside individual queries in set operations does not affect the final combined result's order.

#2 Using column names from the second query in ORDER BY after a set operation.

Wrong approach:

SELECT name FROM class1
UNION
SELECT full_name FROM class2
ORDER BY full_name;

Correct approach:

SELECT name FROM class1
UNION
SELECT full_name FROM class2
ORDER BY name;

Root cause:

Not realizing ORDER BY column names come from the first query's output.

#3 Expecting ORDER BY to sort rows before duplicate removal in UNION.

Wrong approach:

SELECT name FROM class1
UNION
SELECT name FROM class2
ORDER BY name DESC; -- expecting duplicates to be sorted before removal

Correct approach:

SELECT name FROM class1
UNION
SELECT name FROM class2
ORDER BY name DESC; -- duplicates removed first, then sorted

Root cause:

Confusing the order of operations in SQL set operations and sorting.

Key Takeaways

Set operations combine multiple query results into one unified set based on union, intersection, or difference.

ORDER BY sorts the entire combined result after set operations, not individual queries.

Column names for ORDER BY come from the first query in the set operation.

ORDER BY inside individual queries in set operations is ignored for final sorting.

Understanding these rules prevents common errors and helps write clear, efficient SQL queries.

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