PostgreSQL EXCEPT Operator

The EXCEPT operator in PostgreSQL is used to return the rows from the first query that are not present in the second query. Essentially, it performs a "set difference" between two result sets.

How it works:

The the EXCEPT operator compares the results of two SELECT statements.
It returns the rows from the first SELECT that do not exist in the second SELECT.
Both SELECT statements must return the same number of columns, and the corresponding columns should be of compatible data types.

1. Syntax of `EXCEPT`

SELECT columns
FROM table1
EXCEPT
SELECT columns
FROM table2;

The two SELECT statements must have the same number of columns, and the columns must have compatible data types.

2. Example: Basic Use of `EXCEPT`

Scenario:

Let’s assume we have two tables: employees and contractors. We want to find the employees who are not contractors.

`employees` Table:

employee_id	name
1	Alice
2	Bob
3	Charlie
4	David

`contractors` Table:

contractor_id	name
2	Bob
4	David

Query: Find Employees Who Are Not Contractors

SELECT name
FROM employees
EXCEPT
SELECT name
FROM contractors;

Result:

name
Alice
Charlie

Explanation: The query returns the names of employees who do not appear in the contractors table. In this case, Alice and Charlie are not contractors.

3. Example: Using `EXCEPT` with Multiple Columns

You can also use EXCEPT when comparing multiple columns. For example, let’s say we want to find customers who have placed orders but have not returned any products.

`orders` Table:

order_id	customer_id	order_date
1	101	2025-01-01
2	102	2025-01-02
3	103	2025-01-03
4	101	2025-01-04

`returns` Table:

return_id	customer_id	return_date
1	101	2025-01-05
2	102	2025-01-06

Query: Find Customers Who Have Placed Orders but Have Not Returned Products

SELECT customer_id, order_date
FROM orders
EXCEPT
SELECT customer_id, return_date
FROM returns;

Result:

customer_id	order_date
103	2025-01-03
101	2025-01-04

Explanation: The result shows customers who placed orders but did not return products. For example, customer 103 placed an order but never returned any products.

4. `EXCEPT` vs `NOT IN`

While both EXCEPT and NOT IN can be used to find rows in one table that do not exist in another, EXCEPT is usually more efficient for handling set-based operations.

Here’s the equivalent query using NOT IN:

SELECT name
FROM employees
WHERE name NOT IN (SELECT name FROM contractors);

Performance: EXCEPT typically handles set operations more efficiently than NOT IN, especially for large datasets with complex conditions.

5. Important Points About `EXCEPT`

Column Matching: Both SELECT statements must return the same number of columns with compatible data types.
Duplicates: EXCEPT removes duplicate rows from the result set. If you want to retain duplicates, you should use EXCEPT ALL.
EXCEPT ALL: This variant of EXCEPT includes all occurrences of rows in the first result set that are not in the second.

6. Example: Using `EXCEPT ALL`

SELECT name
FROM employees
EXCEPT ALL
SELECT name
FROM contractors;

Difference: Unlike EXCEPT, which eliminates duplicates, EXCEPT ALL preserves all rows, even duplicates, from the first SELECT that are not found in the second.

7. Summary of `EXCEPT`

Operator	Description
`EXCEPT`	Returns rows from the first query that are not in the second query. Removes duplicates.
`EXCEPT ALL`	Similar to `EXCEPT`, but keeps duplicates from the first query.
Column Matching	Both queries must return the same number of columns with compatible data types.
Performance	Generally more efficient than `NOT IN` for set operations.

Would you like to see more complex examples using EXCEPT, or any other SQL operator? 🚀

Souy Soeng

PostgreSQL EXCEPT Operator