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.