UNION, UNION ALL, INTERSECT, and EXCEPT

Objectives

By the end of this lesson, you should be able to:

• Combine the results of two queries with UNION and UNION ALL
• Find rows common to both queries with INTERSECT
• Find rows in one query but not the other with EXCEPT
• Explain the rule both queries must follow to be combined this way

💡 Why this matters: A JOIN combines two tables side by side, column by column. These four operators combine two queries’ results stacked on top of each other instead, same columns, more rows, useful when the data genuinely comes from two separate sources that should be treated as one list.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE contractors (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);

INSERT INTO contractors (first_name, department) VALUES ('Erin', 'Engineering'), ('Robin', 'Design');

contractors is a separate table from employees, but shares the idea of “a person, with a department.” Erin appears in both employees (as a full-time employee) and contractors (apparently also doing contract work), this overlap matters below.

The Rule: Matching Columns

Every operator in this lesson requires both queries to select the same number of columns, in compatible types, PostgreSQL combines them position by position, not by column name.

UNION

SELECT first_name FROM employees WHERE department = 'Engineering'
UNION
SELECT first_name FROM contractors WHERE department = 'Engineering'
ORDER BY first_name;

 first_name
------------
 Erin
 Priya
 Taylor

UNION stacks both result sets together and removes duplicate rows. Erin appears in both queries (as an employee and a contractor), but shows up only once in the combined result, UNION deduplicates by default.

UNION ALL

SELECT first_name FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT first_name FROM contractors WHERE department = 'Engineering'
ORDER BY first_name;

 first_name
------------
 Erin
 Erin
 Priya
 Taylor

UNION ALL stacks both result sets without removing duplicates, Erin appears twice, once from each query. UNION ALL is also faster than UNION, since PostgreSQL doesn’t have to check for and remove duplicates, worth using whenever duplicates genuinely don’t matter or are known not to occur.

INTERSECT

SELECT first_name FROM employees WHERE department = 'Engineering'
INTERSECT
SELECT first_name FROM contractors;

 first_name
------------
 Erin

INTERSECT returns only rows that appear in both result sets, Erin is the only name in Engineering employees that also appears anywhere in contractors.

EXCEPT

SELECT first_name FROM employees WHERE department = 'Engineering'
EXCEPT
SELECT first_name FROM contractors;

 first_name
------------
 Priya
 Taylor

EXCEPT returns rows from the first query that don’t appear in the second, Engineering employees minus anyone who’s also a contractor. Order matters for EXCEPT, unlike UNION and INTERSECT, swapping the two queries would answer a different question (contractors who aren’t Engineering employees).

Try It

1. Write a UNION combining every employees first name with every contractors first name, confirming Erin appears only once.
2. Write the same combination with UNION ALL instead, and count how many total rows come back.
3. Write an INTERSECT finding names that appear in both employees and contractors, regardless of department.
4. Explain, in your own words, why EXCEPT (unlike UNION or INTERSECT) gives a different answer depending on which query is written first.

Recap

• UNION stacks two queries’ results and removes duplicates, UNION ALL stacks them without removing duplicates (and runs faster).
• INTERSECT returns only rows present in both queries, EXCEPT returns rows from the first query missing from the second.
• All four require both queries to select the same number of columns in compatible types, combined by position, not by column name.

Next lesson: this module’s exercises, building reporting queries with subqueries, CTEs, conditional logic, and combined result sets.