Set Operators — UNION & UNION ALL

#Introduction

Set operators combine the results of multiple queries. Though a relatively small topic, it's frequently asked in SQL interviews and widely used in enterprise reporting.

This chapter covers UNION, UNION ALL and the difference between them, the rules for using UNION, ordering combined results, combining multiple tables, real-world use cases, performance considerations and common interview questions.

#What Is a Set Operator?

A set operator combines the results of two or more SELECT queries into a single result set.

Combined output: John, David, Alex, Lisa.

#UNION

UNION combines results and removes duplicate rows:

SELECT employee_name FROM employee
UNION
SELECT manager_name FROM manager;

If employee contains John and David, and manager contains John and Alex:

John appears in both tables but only once in the result. (UNION is shorthand for UNION DISTINCT.)

#UNION ALL

UNION ALL combines results without removing duplicates:

SELECT employee_name FROM employee
UNION ALL
SELECT manager_name FROM manager;

Duplicates remain.

#UNION vs UNION ALL

#Rules for UNION

Rule 1 — Same Number of Columns

-- Correct: both queries return 2 columns
SELECT employee_id, employee_name FROM employee
UNION
SELECT customer_id, customer_name FROM customer;

-- Incorrect
SELECT employee_id FROM employee
UNION
SELECT customer_id, customer_name FROM customer;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Rule 2 — Compatible Data Types

SELECT employee_name FROM employee
UNION
SELECT customer_name FROM customer;

Both columns hold compatible character data. Corresponding columns should have compatible types — query 1 column 1 with query 2 column 1, query 1 column 2 with query 2 column 2, and so on. Columns are matched by position, not by name.

Column Names in UNION Results

The output column names are taken from the first SELECT:

SELECT employee_name AS name FROM employee
UNION
SELECT customer_name FROM customer;

The output column is name — so define meaningful aliases in the first query.

#ORDER BY with UNION

ORDER BY is applied once, to the final combined result:

SELECT employee_name AS name FROM employee
UNION
SELECT customer_name FROM customer
ORDER BY name;

#Combining More Than Two Queries

-- All unique cities across three tables
SELECT city FROM customer
UNION
SELECT city FROM supplier
UNION
SELECT city FROM warehouse;

-- All rows from three yearly tables; duplicates preserved
SELECT * FROM sales_2024
UNION ALL
SELECT * FROM sales_2025
UNION ALL
SELECT * FROM sales_2026;

SELECT * with set operators only works safely if every table has identical columns in the same order — list the columns explicitly in production code.

#Practical Example

employee: (1, John), (2, David). customer: (101, Lisa), (102, Mary).

SELECT employee_name AS name FROM employee
UNION
SELECT customer_name FROM customer;

Adding a Source Column

In enterprise reporting you often need to know where each row came from:

SELECT employee_id   AS id,
       employee_name AS name,
       'EMPLOYEE'    AS source
FROM employee
UNION ALL
SELECT customer_id,
       customer_name,
       'CUSTOMER'
FROM customer;

Extremely useful in consolidated reports.

#Real-World Examples

-- 1. Current + archived orders
SELECT * FROM orders_current
UNION ALL
SELECT * FROM orders_archive;

-- 2. Employee directory: a unique list of people
SELECT employee_name FROM employee
UNION
SELECT contractor_name FROM contractor;

-- 3. Customer email list: unique addresses
SELECT email FROM customer
UNION
SELECT email FROM vendor;

-- 4. Regional sales report
SELECT region, revenue FROM north_sales
UNION ALL
SELECT region, revenue FROM south_sales;

#Performance

UNION needs that extra duplicate-elimination step — MySQL typically builds a temporary table with a unique index to produce distinct rows.

UNION ALL skips it, so it's generally faster (and since MySQL 5.7 can often stream rows without a temporary table).

Which One Should You Use?

Enterprise systems often prefer UNION ALL when duplicates are acceptable or impossible, because unnecessary duplicate elimination adds overhead.

#UNION vs JOIN

These are often confused.

UNION combines results vertically — table A's rows (John, David) followed by table B's rows (Lisa, Mary) give one column of four rows: John, David, Lisa, Mary.

JOIN combines related columns horizontally — employee + department give rows of employee name | department name:

SELECT e.employee_name, d.department_name
FROM employee e
JOIN department d ON e.department_id = d.department_id;

#INTERSECT and EXCEPT in MySQL

-- MySQL 8.0.31+
SELECT employee_id FROM employee
INTERSECT
SELECT employee_id FROM manager;

SELECT employee_id FROM employee
EXCEPT
SELECT employee_id FROM manager;

Emulating INTERSECT

INTERSECT = rows present in both result sets:

-- Using EXISTS
SELECT e.employee_id
FROM employee e
WHERE EXISTS (
    SELECT 1
    FROM manager m
    WHERE m.employee_id = e.employee_id
);

-- Using INNER JOIN
SELECT DISTINCT e.employee_id
FROM employee e
INNER JOIN manager m ON e.employee_id = m.employee_id;

Emulating EXCEPT

EXCEPT = rows in the first result that are not in the second:

SELECT e.employee_id
FROM employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM manager m
    WHERE m.employee_id = e.employee_id
);

NOT EXISTS is safer than NOT IN when the subquery may contain NULL values.

#NULL Behavior

UNION eliminates duplicates across complete rows, and for this purpose NULLs are treated as equal:

SELECT NULL AS value
UNION
SELECT NULL;        -- one row

SELECT NULL AS value
UNION ALL
SELECT NULL;        -- two rows

#Mini Project

Given employee (employee_id, name), contractor (contractor_id, name) and customer (customer_id, name), write queries to:

1. Display all unique people using UNION.
2. Display all people including duplicates using UNION ALL.
3. List all unique cities from the customer and vendor tables.
4. Merge current and archived orders.
5. Add a source column identifying where each record came from.
6. Compare the execution plans of UNION and UNION ALL.

#Practice Problems

Easy

1. Combine two employee lists.
2. Use UNION ALL.
3. Sort combined results.
4. Remove duplicate names.
5. Combine three tables.

Medium

1. Merge yearly sales data.
2. Merge customer and vendor email lists.
3. Create a company directory.
4. Replace UNION with UNION ALL where appropriate.
5. Add a source column to a combined result.

Interview level

1. Explain UNION vs UNION ALL.
2. Why is UNION ALL faster?
3. What are the rules for using UNION?
4. Explain UNION vs JOIN.
5. How would you emulate set intersection or difference in MySQL?

#Key Takeaways

• UNION combines result sets and removes duplicate rows; UNION ALL keeps them and is generally faster.
• All queries must return the same number of columns with compatible types; columns are matched by position.
• Output column names come from the first SELECT.
• Use an explicit ORDER BY when the final order matters.
• UNION combines rows vertically, while JOIN combines related columns horizontally.
• INTERSECT and EXCEPT exist from MySQL 8.0.31; otherwise emulate them with EXISTS / NOT EXISTS.