Set operations with ORDER BY in SQL

Introduction

Set operations combine results from two or more queries into one list. ORDER BY sorts the combined list to make it easier to read or find data.

You want to merge customer lists from two different regions into one list.

You need to find all unique products sold in two different stores and sort them by name.

You want to combine employee names from two departments and order them alphabetically.

You want to see all distinct dates when sales happened in two different years, sorted by date.

Syntax

SQL

SELECT column_list FROM table1
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT column_list FROM table2
ORDER BY column_name [ASC|DESC];

Set operations combine the results of two SELECT queries.

ORDER BY goes at the end to sort the final combined result.

Examples

Combine unique customer names from two tables and sort them alphabetically.

SQL

SELECT name FROM customers_north
UNION
SELECT name FROM customers_south
ORDER BY name ASC;

Combine all products from two stores including duplicates, sorted in reverse alphabetical order.

SQL

SELECT product FROM store_a
UNION ALL
SELECT product FROM store_b
ORDER BY product DESC;

Find employees common to both departments and sort their names.

SQL

SELECT employee FROM dept1
INTERSECT
SELECT employee FROM dept2
ORDER BY employee;

Sample Program

This query combines unique fruit names from two tables and sorts them alphabetically.

SQL

CREATE TABLE fruits1 (name VARCHAR(20));
CREATE TABLE fruits2 (name VARCHAR(20));

INSERT INTO fruits1 VALUES ('Apple'), ('Banana'), ('Cherry');
INSERT INTO fruits2 VALUES ('Banana'), ('Date'), ('Apple');

SELECT name FROM fruits1
UNION
SELECT name FROM fruits2
ORDER BY name ASC;

Important Notes

UNION removes duplicates, UNION ALL keeps duplicates.

ORDER BY sorts the final combined result, not individual queries.

All SELECT queries must have the same number of columns with compatible types.

Summary

Set operations combine results from multiple queries into one list.

ORDER BY sorts the combined results for easier reading.

Use UNION to remove duplicates, UNION ALL to keep them.

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