Anti-Joins, Semi-Joins & Non-Equi Joins

These aren't special SQL keywords — they're patterns built from tools you already know (JOIN, EXISTS, IN), each answering a specific kind of question.

Semi-Join — "At Least One Match Exists"

Returns rows from the left table where a match exists in the right table, without duplicating rows if there are multiple matches (unlike a regular join).

-- departments that have at least one employee
SELECT * FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);

-- equivalent using IN
SELECT * FROM departments
WHERE id IN (SELECT department_id FROM employees);

A regular INNER JOIN here would return one row per matching employee, potentially duplicating each department — the semi-join pattern avoids that entirely.

Anti-Join — "No Match Exists"

The opposite: rows from the left table with zero matches in the right table.

-- departments with NO employees at all
SELECT * FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.id
);

An equally common pattern using LEFT JOIN:

SELECT d.* FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;
-- if there's no match, e.id comes back NULL — that's how we detect "no employees"

Watch out with NOT IN — if the subquery can return even one NULL, NOT IN returns zero rows for the entire query, silently. Prefer NOT EXISTS to avoid this trap:

-- DANGEROUS if department_id can be NULL for some employees:
SELECT * FROM departments WHERE id NOT IN (SELECT department_id FROM employees);

-- SAFE:
SELECT * FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

Non-Equi Join — Joining on Something Other Than =

A regular join condition doesn't have to be an equality check — any boolean expression works.

-- match each employee to the salary band their salary falls into
SELECT e.name, e.salary, b.band_name
FROM employees e
JOIN salary_bands b
  ON e.salary BETWEEN b.min_salary AND b.max_salary;

-- find pairs of employees within $1000 of each other's salary
SELECT e1.name, e2.name, e1.salary, e2.salary
FROM employees e1
JOIN employees e2
  ON e1.id < e2.id AND ABS(e1.salary - e2.salary) < 1000;

Common Errors & Troubleshooting

• NOT IN silently returning zero rows even though matches should exist — the classic trap: if the subquery's column contains even one NULL, the entire NOT IN comparison becomes unknown for every row — always prefer NOT EXISTS.
• A "semi-join" via INNER JOIN returning duplicate rows — a plain JOIN returns one row per matching pair, potentially duplicating the left-side row if there are multiple matches — use EXISTS/IN instead when you only need "does at least one match exist," not the matched data itself.
• A non-equi join producing far more rows than expected — an inequality join condition (like salary BETWEEN min AND max) can match many more rows per pair than an equality join would; double-check the condition is as tight as intended.

Key Takeaways

• Semi-joins (EXISTS/IN) answer "does a match exist" without duplicating rows; anti-joins (NOT EXISTS/LEFT JOIN ... WHERE NULL) answer "does no match exist."
• Prefer NOT EXISTS over NOT IN whenever the subquery's column might contain NULL — NOT IN silently breaks in that case.
• A join condition can be any boolean expression, not just = — range and inequality joins are perfectly valid SQL.