# Semi-Joins and Anti-Joins — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/j-semi

> EXISTS, NOT EXISTS and the NOT IN trap.

## "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.

```sql
-- 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.

**Quiz:** Why can NOT IN (subquery) return no rows at all?

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

*Answer:* If the subquery returns a NULL, every NOT IN comparison is unknown. Use NOT EXISTS instead.