EXCEPT / MINUS

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.

---

What it does

EXCEPT returns rows from the first query that do not appear in the second query. Oracle uses MINUS instead of EXCEPT.

Problem

Display employees who are currently working but are not present in the former employees table. -- SQL Server / PostgreSQL

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

-- Oracle

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

Result