SQL Set Operators: UNION, UNION ALL, EXCEPT, and INTERSECT

Set operators in SQL allow developers and data analysts to combine the results of multiple SELECT statements into a single, cohesive result set. While SQL joins merge data horizontally by combining columns across tables, set operators merge data vertically by appending rows from one query beneath another.

Understanding how set operators process data behind the scenes, adhering to their mandatory syntax rules, and choosing the appropriate operator for specific analytical or engineering scenarios is essential for building scalable queries and robust data pipelines.

1. Fundamental Premise: Joins vs. Set Operators

Both SQL Joins and SQL Set Operators combine data from multiple tables or queries into a single output grid. However, their structural direction and underlying requirements are fundamentally different.

Key Differences

• Joins (Horizontal Union): Merge columns from a left table and a right table side-by-side based on a matching key condition (e.g., INNER JOIN, LEFT JOIN, FULL JOIN).
• Set Operators (Vertical Union): Stack rows from multiple queries vertically into one continuous dataset (e.g., UNION, UNION ALL, EXCEPT, INTERSECT).

2. Basic Syntax and Query Construction

To use a set operator, place the operator keyword between two or more valid SELECT queries:

SELECT column_1, column_2, ...
FROM table_A

SET_OPERATOR -- UNION | UNION ALL | EXCEPT | INTERSECT

SELECT column_1, column_2, ...
FROM table_B;

SQL executes each SELECT query individually, processes the set operation across the returned row sets, and returns a unified result grid.

3. The 6 Mandatory Rules of SQL Set Operators

For SQL to execute a set operation without throwing runtime errors or corrupting data quality, queries must satisfy six fundamental rules.

Rule 1: Placement of the ORDER BY Clause

Individual SELECT queries within a set operation cannot contain their own ORDER BY clauses. The ORDER BY clause can be used only once, placed strictly at the very end of the combined statement to sort the final result set.

Note: Clauses like WHERE, JOIN, GROUP BY, and HAVING are fully permitted inside each individual SELECT statement to filter or aggregate data prior to set processing.

Invalid Query (Syntax Error):

-- FAILS: ORDER BY cannot appear in individual subqueries
SELECT first_name, last_name FROM sales.customers
ORDER BY first_name
UNION
SELECT first_name, last_name FROM sales.employees
ORDER BY first_name;

Valid Query:

-- CORRECT: ORDER BY is placed at the very end
SELECT first_name, last_name FROM sales.customers
UNION
SELECT first_name, last_name FROM sales.employees
ORDER BY first_name;

Rule 2: Equal Number of Columns

Every SELECT query in the set operation must project the exact same number of columns.

Invalid Query (Column Count Mismatch):

-- FAILS: Query 1 returns 3 columns, Query 2 returns 2 columns
SELECT customer_id, first_name, last_name FROM sales.customers
UNION
SELECT first_name, last_name FROM sales.employees;

Database Error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Valid Query:

-- CORRECT: Both queries project exactly 2 columns
SELECT first_name, last_name FROM sales.customers
UNION
SELECT first_name, last_name FROM sales.employees;

Rule 3: Data Type Compatibility

Corresponding columns across all queries must share compatible data types. SQL will attempt implicit type conversion based on the data types defined in the first query. If types are incompatible (e.g., attempting to map a VARCHAR string to an INT), SQL throws a conversion error.

Invalid Query (Data Type Mismatch):

-- FAILS: Column 1 maps customer_id (INT) to first_name (VARCHAR)
SELECT customer_id, last_name FROM sales.customers
UNION
SELECT first_name, last_name FROM sales.employees;

Database Error: Conversion failed when converting the varchar value 'Frank' to data type int.

Valid Query:

-- CORRECT: Column 1 maps customer_id (INT) to employee_id (INT)
SELECT customer_id, last_name FROM sales.customers
UNION
SELECT employee_id, last_name FROM sales.employees;

Rule 4: Sequential Column Order

SQL maps columns strictly by positional index (1st column to 1st column, 2nd column to 2nd column), rather than matching column names.

Problematic Query (Mismatched Positional Order):

-- FAILS: Column 1 maps last_name (VARCHAR) to employee_id (INT)
SELECT last_name, customer_id FROM sales.customers
UNION
SELECT employee_id, last_name FROM sales.employees;

Even though both queries contain an integer ID and a string last_name, SQL maps last_name (position 1) to employee_id (position 1), triggering an implicit conversion error. The positional order of columns across all queries must match.

Rule 5: Column Naming and Alias Precedence

The column names and aliases shown in the final output grid are strictly determined by the first SELECT query. Subsequent aliases defined in lower queries are completely ignored by the SQL engine.

SELECT 
    customer_id AS entity_id,  -- <--- DEFINES OUTPUT COLUMN NAME
    last_name AS surname        -- <--- DEFINES OUTPUT COLUMN NAME
FROM sales.customers

UNION

SELECT 
    employee_id AS emp_code,   -- Ignored for grid header
    last_name AS family_name   -- Ignored for grid header
FROM sales.employees;

Output Grid Headers: entity_id | surname

Best Practice: Apply column aliases strictly to the first SELECT query in the statement. Adding aliases to lower queries adds noise without altering output headers.

Rule 6: Logical/Semantic Data Mapping (Developer Responsibility)

SQL enforces data type compatibility, but it cannot evaluate semantic logic. If two columns share the same data type (e.g., both are VARCHAR), SQL will successfully execute the query even if you accidentally map a first_name to a last_name.

Semantically Corrupted Query (Executes without Error):

-- EXECUTES SUCCESSFULLY BUT PRODUCES DIRTY DATA
SELECT first_name, last_name FROM sales.customers
UNION
SELECT last_name, first_name FROM sales.employees;

The database engine blindly stacks positional string data. Developers are strictly responsible for verifying that positionally aligned columns contain logically equivalent data concepts.

4. Operational Breakdown: UNION

The UNION operator combines all rows from multiple queries and automatically removes duplicate rows across the combined result set. Every unique row appears exactly once in the final output.

Execution Step-by-Step

Code Example

SELECT first_name, last_name FROM sales.employees
UNION
SELECT first_name, last_name FROM sales.customers;

5. Operational Breakdown: UNION ALL

The UNION ALL operator combines all rows from multiple queries including all duplicates. If a row appears in Query 1 and also appears in Query 2, both rows will be retained in the final output grid.

Execution Step-by-Step

Performance Comparison: UNION vs. UNION ALL

• UNION ALL is significantly faster than UNION. UNION must perform a distinct/sort operation in memory to identify and drop duplicate rows across datasets. UNION ALL simply streams and appends row buffers.
• Engineering Rule: If you know your underlying source datasets are mutually exclusive (or if business logic requires keeping duplicates), always default to UNION ALL to optimize query performance.

Code Example

SELECT first_name, last_name FROM sales.employees
UNION ALL
SELECT first_name, last_name FROM sales.customers;

6. Operational Breakdown: EXCEPT (or MINUS)

The EXCEPT operator (referred to as MINUS in Oracle and MySQL) performs set subtraction. It returns all distinct rows from the first query that are not present in the second query.

Crucial Rule: EXCEPT is the only set operator where query order strictly alters the logical output.

Execution Step-by-Step (Scenario A)

Code Example

-- Find employees who are NOT customers
SELECT first_name, last_name FROM sales.employees
EXCEPT
SELECT first_name, last_name FROM sales.customers;

7. Operational Breakdown: INTERSECT

The INTERSECT operator returns only distinct rows that are common to both queries. If a row exists in Query 1 but does not exist in Query 2, it is omitted. Like UNION, INTERSECT automatically removes duplicate entries from the output set.

Execution Step-by-Step

Query order does not alter the mathematical result of an INTERSECT operation (except for column naming precedence from Query 1).

Code Example

-- Find individuals who are BOTH employees AND customers
SELECT first_name, last_name FROM sales.employees
INTERSECT
SELECT first_name, last_name FROM sales.customers;

8. Tabular Synthesis of Set Operators

9. Practical Engineering Pattern: Data Consolidation & Pre-Reporting

In enterprise relational models, database architects often split entities into disparate tables for organizational or performance reasons (e.g., entity-type partitioning or historical date-sharding). Set operators allow developers to centralize these fragmented datasets prior to performing aggregations or analytical reporting.

Pattern 1: Multi-Entity Domain Consolidation

Instead of writing repetitive analytical queries across employees, customers, suppliers, and students tables, merge them into a single central dataset.

Pattern 2: Historical Partition Merging

When historical orders are split across active tables (sales.orders) and cold archive tables (sales.orders_archive), use UNION or UNION ALL to build a unified reporting layer:

-- Consolidated orders query prior to aggregation
SELECT order_id, customer_id, product_id, order_date, order_amount
FROM sales.orders

UNION ALL

SELECT order_id, customer_id, product_id, order_date, order_amount
FROM sales.orders_archive;

10. Production Best Practices: Schema Drift & Source Lineage Tracking

1. Avoid Explicit SELECT * in Set Operations

Using SELECT * in production set operations introduces severe vulnerability to schema drift. If a database administrator adds, reorders, or drops a column in sales.orders without modifying sales.orders_archive, SELECT * set operations will immediately fail due to column count mismatches or position/type misalignment.

Vulnerable Code:

-- RISKY: Breaks if table schemas diverge over time
SELECT * FROM sales.orders
UNION ALL
SELECT * FROM sales.orders_archive;

Production Standard:

-- SECURE: Explicitly declare every column in strict order
SELECT 
    order_id,
    product_id,
    customer_id,
    order_date,
    sales_amount
FROM sales.orders

UNION ALL

SELECT 
    order_id,
    product_id,
    customer_id,
    order_date,
    sales_amount
FROM sales.orders_archive;

2. Injecting Static Literals for Source Lineage Tracking

When combining rows from multiple datasets into a single grid, tracking the origin of each row is vital for data auditing and analytical debugging. You can accomplish this by projecting static literal strings as artificial columns.

SELECT 
    'active_orders' AS source_table, -- Hardcoded lineage tag
    order_id,
    customer_id,
    order_date,
    sales_amount
FROM sales.orders

UNION ALL

SELECT 
    'archived_orders' AS source_table, -- Hardcoded lineage tag
    order_id,
    customer_id,
    order_date,
    sales_amount
FROM sales.orders_archive

ORDER BY order_id ASC;

Output Grid:

Notice that order 1001 exists in both tables without conflict; the source_table tag provides unambiguous context for downstream analysts.

11. Advanced Data Engineering Patterns: Delta Extraction & Migration QA

Pattern 1: Incremental Pipeline Load (Delta Extraction)

Data engineers building ETL pipelines often need to extract only newly added records from an operational source system to load into a target Data Warehouse.

-- ETL Pipeline: Isolate newly created records from today's operational extract
SELECT customer_id, email, address_last_updated
FROM stage.source_customers_day_2

EXCEPT

SELECT customer_id, email, address_last_updated
FROM dw.dim_customers_day_1;

Pattern 2: Bidirectional Data Completeness Testing (QA / Migration Verification)

When migrating datasets between systems (e.g., moving data from legacy Database A to modern Database B), row counts alone do not prove data fidelity. Engineers perform Bidirectional EXCEPT Validation.

Test 1: Check for Source Records Missing in Target (Forward Pass)

SELECT customer_id, first_name, last_name, email FROM db_a.customers
EXCEPT
SELECT customer_id, first_name, last_name, email FROM db_b.customers;

Expected Result: 0 rows returned (Empty set indicates no legacy records were lost during migration).

Test 2: Check for Unintended Phantom Records in Target (Reverse Pass)

SELECT customer_id, first_name, last_name, email FROM db_b.customers
EXCEPT
SELECT customer_id, first_name, last_name, email FROM db_a.customers;

Expected Result: 0 rows returned (Empty set indicates no corrupted or extraneous records were created in the target).

Validation Logic: If both Query 1 and Query 2 return empty result sets, Datasets A and B are proven to be 100% structurally and semantically identical.

12. Summary and Key Takeaways

• Vertical vs. Horizontal Merging: Set operators stack query results vertically (adding rows), whereas joins combine tables horizontally (adding columns).
• Column Alignment Constraints: Every query within a set operation must project the exact same count of columns in the exact same positional data-type order.
• First Query Precedence: The first SELECT statement in a set operation controls column aliases, grid output headers, and implicit data type definitions for the entire statement.
• Global Sorting: An ORDER BY clause can be used only once per set operation and must strictly be placed at the very end of the final query.
• Default to UNION ALL for Speed: UNION performs expensive deduplication checks in memory. Use UNION ALL whenever data is guaranteed distinct or when duplicate retention is acceptable.
• Asymmetric Nature of EXCEPT: EXCEPT returns distinct records from the first query that do not exist in the second query; changing the query sequence changes the output.
• Symmetric Commonality with INTERSECT: INTERSECT identifies rows shared in common across datasets, acting as a vertical row-based equivalent of an inner join.
• Avoid SELECT *: Always explicitly enumerate projected columns in set operations to insulate code against schema drift errors.
• Production Lineage: Project literal static strings (e.g., 'table_name' AS source_table) to retain source lineage context across merged datasets.
• Automated Data Quality: Use bidirectional EXCEPT queries to validate dataset equality during data engineering migrations and ETL testing pipelines.