Semi-Joins and Anti-Joins

"Has at least one" and "has none"

A semi-join keeps rows that have at least one match without duplicating them: WHERE EXISTS (subquery). An anti-join keeps rows with no match: WHERE NOT EXISTS (...) or LEFT JOIN ... WHERE right.id IS NULL. Avoid NOT IN (subquery) when the subquery can return NULL: one NULL makes every comparison unknown, so the query returns no rows. PostgreSQL plans EXISTS and NOT EXISTS efficiently as semi and anti joins.

EXISTS, NOT EXISTS and NOT IN with a NULL, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Engineering and Sales have well-paid staff; Legal has nobody. NOT IN returns 0 rows, not 1, because Nina's dept_id is NULL.

-- Departments with at least one employee earning >= 150000 (semi-join)
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id AND e.salary >= 150000)
ORDER BY d.name;

-- Departments with no employees (anti-join)
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

-- The NOT IN trap: Nina has dept_id NULL, so NOT IN returns nothing
SELECT count(*) AS not_in_rows FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);

Output:

    name     
-------------
 Engineering
 Sales
(2 rows)

 name  
-------
 Legal
(1 row)

 not_in_rows 
-------------
           0
(1 row)

Default to NOT EXISTS

It is NULL-safe and usually as fast or faster than NOT IN.

Quick check: Why can NOT IN (subquery) return no rows at all?

• NOT IN is not supported
• If the subquery returns a NULL, every NOT IN comparison is unknown
• It only works on numbers
• It requires an index