Set Operators

Set operators combine the results of two or more SELECT statements into a single result set. Unlike JOINs, which match rows horizontally across tables, set operators stack results vertically — placing one query's rows on top of another's.

Oracle supports four set operators: UNION, UNION ALL, INTERSECT, and MINUS. They behave like mathematical set operations: union, intersection, and difference.

Why Use Set Operators?

Set operators answer questions that are awkward to express with JOINs alone:

• UNION — "Show me all customers and all employees in one combined list."
• INTERSECT — "Which email addresses appear in both the customer table and the employee table?"
• MINUS — "Which customers have never placed an order?"

You could rewrite each of these with subqueries or JOINs, but set operators are often clearer and sometimes faster.

Tables Used in This Chapter

We'll use two small tables to illustrate every operator:

employees_us:

employees_uk:

Bob appears in both tables with the same email (he's a dual-region employee).

UNION — Combine and Deduplicate

UNION stacks rows from two queries and removes duplicates. The result is a unique set.

SELECT name, email FROM employees_us
UNION
SELECT name, email FROM employees_uk;

Result:

Bob appears only once because UNION removes duplicates after combining.

Deduplication compares every column — two rows must match on name AND email to be considered duplicates. If two Bobs had different emails, both would appear.

UNION ALL — Combine, Keep Duplicates

UNION ALL is identical to UNION except it does not remove duplicates. Every row from every query appears in the output.

SELECT name, email FROM employees_us
UNION ALL
SELECT name, email FROM employees_uk;

Result:

Bob appears twice — once from each table.

INTERSECT — Rows in Both Queries

INTERSECT returns only rows that appear in both queries. It's the equivalent of asking "what overlaps?"

SELECT name, email FROM employees_us
INTERSECT
SELECT name, email FROM employees_uk;

Result:

Only Bob's row appears in both tables, so only Bob is returned. INTERSECT also removes duplicates within the result.

Real-world use case — find emails that exist in two systems:

-- Emails that appear in BOTH the marketing list and the customer database
SELECT email FROM marketing_subscribers
INTERSECT
SELECT email FROM customers;

MINUS — Rows in First Query, Not in Second

MINUS returns rows from the first query that do not appear in the second. Oracle's MINUS is the same as ANSI SQL's EXCEPT (Oracle 21c also accepts EXCEPT as a synonym).

SELECT name, email FROM employees_us
MINUS
SELECT name, email FROM employees_uk;

Result:

Bob was removed because his row also exists in the UK table. David and Eve were never in the US list to begin with, so they're not in the output.

Real-world use case — find customers who have never ordered:

SELECT customer_id FROM customers
MINUS
SELECT customer_id FROM orders;

Column Compatibility Rules

All set operators have strict rules about the queries they combine:

1. Same number of columns in every SELECT
2. Compatible data types in each column position (VARCHAR2 matches CHAR, NUMBER matches NUMBER, dates match dates)
3. Column names come from the first query — names in subsequent SELECTs are ignored

-- This works — names in second query are ignored
SELECT name, email FROM employees_us
UNION
SELECT first_name, contact_email FROM employees_uk;

The result columns will be called name and email (from the first query).

-- This FAILS — column count mismatch
SELECT name, email FROM employees_us
UNION
SELECT name FROM employees_uk;
-- ORA-01789: query block has incorrect number of result columns

-- This FAILS — incompatible types in column 2
SELECT name, hire_date FROM employees
UNION
SELECT name, salary    FROM employees;
-- ORA-01790: expression must have same datatype as corresponding expression

ORDER BY With Set Operators

ORDER BY is allowed only once, at the very end of the entire compound query. It applies to the combined result, not to individual SELECTs.

SELECT name, email FROM employees_us
UNION
SELECT name, email FROM employees_uk
ORDER BY name;

Result:

You can also order by column position:

... ORDER BY 1, 2;   -- order by first column, then second

-- ERROR: ORDER BY allowed only at the end
SELECT name FROM employees_us
ORDER BY name             -- ✗ not allowed inside a set operation
UNION
SELECT name FROM employees_uk;

Operator Precedence

When you combine multiple set operators, evaluation follows these rules:

1. INTERSECT has higher precedence than UNION and MINUS (Oracle 21c+)
2. In versions before 21c, all set operators have equal precedence and are evaluated top-to-bottom
3. Use parentheses to make precedence explicit and your intent clear

-- Ambiguous — relies on default precedence:
SELECT email FROM a
UNION
SELECT email FROM b
INTERSECT
SELECT email FROM c;

-- Clearer — parentheses make intent explicit:
SELECT email FROM a
UNION
(SELECT email FROM b
 INTERSECT
 SELECT email FROM c);

Set Operators vs JOINs

Many queries can be expressed either way. Set operators are usually the clearer choice when:

• The two SELECTs return the same columns from different sources
• You want a vertical combination (stacking rows), not a horizontal one
• You're testing set membership (does this value exist in another set?)

For "customers without orders", a JOIN with WHERE IS NULL (the anti-join pattern) is usually faster than MINUS because the optimiser can use indexes more efficiently — but MINUS is often easier to read.

Worked Example — Auditing Two Lists

You receive a "current employees" CSV and a "payroll" CSV. Are they in sync?

-- 1. Anyone in payroll but not in employees (orphan payment risk):
SELECT emp_id FROM payroll
MINUS
SELECT emp_id FROM employees;

-- 2. Anyone in employees but not in payroll (unpaid risk):
SELECT emp_id FROM employees
MINUS
SELECT emp_id FROM payroll;

-- 3. Employees correctly in both:
SELECT emp_id FROM employees
INTERSECT
SELECT emp_id FROM payroll;

-- 4. Total unique IDs across both sources:
SELECT emp_id FROM employees
UNION
SELECT emp_id FROM payroll;

This is a classic data reconciliation pattern — set operators express it more directly than the equivalent JOINs.

Performance Notes

Tips:

• Push filters (WHERE) inside each SELECT, not after the set operation
• Add indexes on the columns being matched (especially for INTERSECT and MINUS)
• Use UNION ALL + DISTINCT only if you specifically want a DISTINCT (rarely faster than UNION)
• For "exists / not exists" semantics on large tables, anti-joins or EXISTS clauses often beat MINUS

-- Less efficient — filter applied after combining everything
SELECT * FROM (
  SELECT name, dept_id FROM employees_us
  UNION ALL
  SELECT name, dept_id FROM employees_uk
) WHERE dept_id = 50;

-- More efficient — filters push into each query
SELECT name, dept_id FROM employees_us WHERE dept_id = 50
UNION ALL
SELECT name, dept_id FROM employees_uk WHERE dept_id = 50;

Common Errors

Interview Corner

-- Slow on large tables:
SELECT customer_id FROM orders_2023 UNION SELECT customer_id FROM orders_2024;

-- Fast — years can't overlap so dedup is wasted work:
SELECT customer_id FROM orders_2023 UNION ALL SELECT customer_id FROM orders_2024;

SELECT id FROM a
MINUS
SELECT id FROM b;

SELECT a.id
FROM   a
LEFT JOIN b ON a.id = b.id
WHERE  b.id IS NULL;

SELECT a.id
FROM   a
WHERE  NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

-- Normalised intersect
SELECT UPPER(TRIM(email)) FROM list_a
INTERSECT
SELECT UPPER(TRIM(email)) FROM list_b;

Related Topics

• Subqueries — set operators are often combined with subqueries in WHERE clauses
• Joins — anti-joins (LEFT JOIN ... WHERE IS NULL) are usually faster than MINUS
• Aggregate Functions — use GROUP BY inside each SELECT before UNION-ing the aggregated results
• CTEs & Procedures — extract complex UNION/INTERSECT logic into a CTE for readability
• Performance — set operators trigger sort-merge plans; check execution plans on large datasets