INTERSECT

INTERSECT returns only the rows that exist in both result sets.

Source data — Pattern 7: Set Operators

All queries run against the shared employees table (see schema.sql). This pattern introduces the former_employees table. Topic 44 also introduces a retired_employees table to demonstrate combining multiple set operators in a single query.

INTERSECT — source table 1

INTERSECT — source table 2

INTERSECT — source table 3

What it does

INTERSECT returns only the rows that exist in both result sets. Both queries must have the same number of columns with compatible data types.

Problem

Find employees who appear in both the current employees and former employees tables.

SELECT first_name, last_name
FROM employees
INTERSECT
SELECT first_name, last_name
FROM former_employees;

Result

INTERSECT — result 1

SQL · Pattern 7 — Set Operators