12. Set Operations · Data Engineering

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.

sql

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.

sql

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.

sql

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.

sql

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.