++++

Data Engineering

May 2026×7 min read

Combine compatible result sets with UNION and UNION ALL, and understand when duplicates should be removed or preserved.

12. Set Operations

Driptanil DattaSoftware Developer

Set Operations

Set operations combine the results of multiple SELECT statements. In MySQL basics, the most common ones are UNION and UNION ALL.

UNION

UNION combines results and removes duplicate rows.

SELECT email
FROM customers
WHERE country = "USA"
 
UNION
 
SELECT email
FROM library_members
WHERE status = "active";

If the same email appears in both result sets, it appears once in the final result.

UNION ALL

UNION ALL combines results and keeps duplicates.

SELECT city
FROM customers
 
UNION ALL
 
SELECT city
FROM employees;

Use UNION ALL when duplicates are meaningful or when you do not need duplicate removal. It is usually faster than UNION.

Column compatibility

Each SELECT must return the same number of columns in the same order.

SELECT
  customer_id AS entity_id,
  "customer" AS entity_type,
  city
FROM customers
 
UNION ALL
 
SELECT
  employee_id AS entity_id,
  "employee" AS entity_type,
  office_city AS city
FROM employees;

The column names in the final output come from the first SELECT.

Sorting union results

Put ORDER BY at the end of the full set operation.

SELECT email
FROM customers
 
UNION
 
SELECT email
FROM library_members
 
ORDER BY email;

Use set operations when you are stacking similar result sets vertically. Use joins when you are combining related columns horizontally.