SQL - UNION Operator

SQL - Combining Results with UNION

The SQL UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It’s a powerful tool for aggregating data from multiple tables that share a similar structure.

The Rules of UNION

To successfully use UNION, the SELECT statements involved must be “union compatible”, which means they must follow these rules:

• Same Number of Columns: Each SELECT statement must retrieve the same number of columns.
• Compatible Data Types: The data types of corresponding columns in each SELECT statement must be compatible (e.g., you can union an INT with a SMALLINT, but not with a DATE).
• Same Order: The columns must be in the same order in each SELECT statement.

The column names in the final result set are taken from the first SELECT statement.

UNION vs. UNION ALL

This is a critical distinction for both correctness and performance.

• UNION: Removes duplicate rows from the final result set. To do this, it must perform an extra processing step (typically a sort or hash) to identify and eliminate duplicates. This makes it slower.
• UNION ALL: Includes all rows from all SELECT statements, including any duplicates. It simply appends the results and is significantly faster because it skips the de-duplication step.

Best Practice: Always use UNION ALL unless you have a specific requirement to remove duplicate rows. The performance gain can be substantial on large result sets.

Example: UNION vs UNION ALL

Consider two tables: one for ACTIVE_EMPLOYEES and one for FORMER_EMPLOYEES.

CREATE TABLE ACTIVE_EMPLOYEES (ID INT, NAME VARCHAR(100));
CREATE TABLE FORMER_EMPLOYEES (ID INT, NAME VARCHAR(100));

INSERT INTO ACTIVE_EMPLOYEES VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO FORMER_EMPLOYEES VALUES (2, 'Bob'), (3, 'Charlie');

Using UNION to get a unique list of all employees:

SELECT ID, NAME FROM ACTIVE_EMPLOYEES
UNION
SELECT ID, NAME FROM FORMER_EMPLOYEES;
-- Result: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')

Using UNION ALL to get the full list, including duplicates:

SELECT ID, NAME FROM ACTIVE_EMPLOYEES
UNION ALL
SELECT ID, NAME FROM FORMER_EMPLOYEES;
-- Result: (1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Charlie')

Combining with Other Clauses

Clauses like WHERE and ORDER BY can be used with UNION, but their placement is important.

• WHERE: A WHERE clause can be applied to each individual SELECT statement to filter its results before the union occurs.
• ORDER BY and LIMIT: These clauses are applied to the final, combined result set. They must appear only once, at the very end of the entire statement.

Example: Filtering and Ordering

SELECT NAME FROM ACTIVE_EMPLOYEES WHERE ID > 1
UNION ALL
SELECT NAME FROM FORMER_EMPLOYEES WHERE NAME LIKE 'C%'
ORDER BY NAME DESC;

This query takes ‘Bob’ from the first table and ‘Charlie’ from the second, combines them, and then sorts the final result to produce: (‘Charlie’, ‘Bob’).

Using Aliases to Reshape Data

UNION is excellent for creating a unified view of data from different sources. You can add literal values to act as new columns, such as a ‘source’ or ‘type’ field.

Example: Adding a Status Column

SELECT NAME, 'Active' AS Status FROM ACTIVE_EMPLOYEES
UNION ALL
SELECT NAME, 'Former' AS Status FROM FORMER_EMPLOYEES;

This produces a single, clean list with each person’s status.

NAME	Status
Alice	Active
Bob	Active
Bob	Former
Charlie	Former

Other Set Operators: INTERSECT and EXCEPT

SQL provides two other set operators that are also very useful:

• INTERSECT: Returns only the rows that appear in both result sets. (e.g., SELECT NAME FROM ACTIVE_EMPLOYEES INTERSECT SELECT NAME FROM FORMER_EMPLOYEES; would return ‘Bob’).
• EXCEPT (or MINUS in Oracle): Returns rows from the first result set that do not appear in the second result set. (e.g., SELECT NAME FROM ACTIVE_EMPLOYEES EXCEPT SELECT NAME FROM FORMER_EMPLOYEES; would return ‘Alice’).