SQL - EXCEPT Operator

SQL Set Operations: EXCEPT, INTERSECT, and UNION

Introduction to SQL Set Operators

SQL provides powerful set operators to combine the results of two or more SELECT statements into a single result set. These operators—EXCEPT, INTERSECT, and UNION—behave like their counterparts in mathematical set theory.

Rules for Set Operators:

1. The SELECT statements must have the same number of columns.
2. The data types of corresponding columns must be compatible.
3. By default, all these operators return distinct rows. Use the ALL keyword (e.g., UNION ALL) to include duplicates.

Let’s use two simple tables for our examples: full_time_employees and managers.

CREATE TABLE full_time_employees (employee_name VARCHAR(50));
CREATE TABLE managers (manager_name VARCHAR(50));

INSERT INTO full_time_employees VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');
INSERT INTO managers VALUES ('Charlie'), ('David'), ('Eve');

The SQL EXCEPT Operator: Finding the Difference

The EXCEPT operator returns all distinct rows from the first SELECT statement that are not present in the second SELECT statement. It finds the difference between two sets.

**RDBMS Support:**
- **Supported:** PostgreSQL, SQL Server, SQLite.
- **Not Supported:** MySQL (see alternative below).
- **Alternative Name:** Oracle uses the `MINUS` operator for the same functionality.

Example: Find Employees Who Are Not Managers

SELECT employee_name FROM full_time_employees
EXCEPT
SELECT manager_name FROM managers;

This query returns ‘Alice’ and ‘Bob’ because they are in the full_time_employees table but not in the managers table.

employee_name
Alice
Bob

The SQL INTERSECT Operator: Finding Common Ground

The INTERSECT operator returns all distinct rows that are present in the results of both SELECT statements. It finds the common elements.

Example: Find Employees Who Are Also Managers

SELECT employee_name FROM full_time_employees
INTERSECT
SELECT manager_name FROM managers;

This query returns ‘Charlie’ and ‘David’ because their names appear in both tables.

employee_name
Charlie
David

The SQL UNION Operator: Combining Results

The UNION operator combines the results of two or more SELECT statements into a single result set, removing any duplicate rows. To keep duplicates, use UNION ALL.

Example: Get a List of All People (Employees and Managers)

SELECT employee_name FROM full_time_employees
UNION
SELECT manager_name FROM managers;

This returns a unique list of all names: ‘Alice’, ‘Bob’, ‘Charlie’, ‘David’, and ‘Eve’.

Handling Databases without EXCEPT (like MySQL)

If your database, like MySQL, does not support EXCEPT, you can achieve the same result using a LEFT JOIN and checking for NULL values. This is a very common and important SQL pattern.

The LEFT JOIN ... WHERE ... IS NULL Pattern

To find full-time employees who are not managers, we can LEFT JOIN the employees table to the managers table. For employees who are not managers, the columns from the managers table will be NULL.

SELECT
    e.employee_name
FROM
    full_time_employees AS e
LEFT JOIN
    managers AS m ON e.employee_name = m.manager_name
WHERE
    m.manager_name IS NULL;

This query produces the exact same result as the EXCEPT example: ‘Alice’ and ‘Bob’. This pattern is a fundamental tool for any SQL developer, especially when working with MySQL.