Semi Join

Definition

A semi join returns rows from one table that have AT LEAST ONE matching row in another — WITHOUT duplicating the row for each match, and without pulling in any columns from the other table. It answers "does a match exist?" not "what does the match look like?"

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT d.id, d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

This returns Engineering and Sales (each has at least one employee) — each appearing EXACTLY ONCE, regardless of how many employees they actually have. Contrast this with an ordinary JOIN, which would duplicate Engineering once per employee (16.10, 16.11) — a semi join specifically avoids that duplication, since it only cares about existence, not the matched rows' data.

Edge Cases and Pitfalls

• EXISTS is the standard way to express a semi join in SQL — there's no dedicated SEMI JOIN keyword in mainstream SQL, similar to how anti join has no dedicated keyword either.
• IN (subquery) can also express a semi join (WHERE d.id IN (SELECT department_id FROM employees)), but EXISTS is often preferred: it's immune to the IN+NULL gotcha in certain formulations, and can stop scanning as soon as one match is confirmed rather than necessarily building a full list of matching values first.
• Semi join is exactly the technique to use INSTEAD OF JOIN ... DISTINCT when the goal is "which X have at least one related Y" — using an ordinary join plus DISTINCT to remove duplicates WORKS, but does more work than necessary (matching every row, then deduplicating) compared to a semi join's "stop at first match" efficiency.

Key Takeaways

• Semi join finds rows with at least one match elsewhere, returning each qualifying row exactly once, with no columns pulled from the other table.
• EXISTS is the standard way to express it; no dedicated SEMI JOIN keyword exists.
• Prefer semi join (EXISTS) over JOIN + DISTINCT for pure "does a match exist" questions — it's a more direct, efficient expression of the actual question.