anti join
A query that returns the rows of one table that have no matching row in another, such as products nobody has ordered.
In more detail
Write it as a LEFT JOIN followed by WHERE right_key IS NULL, or with NOT EXISTS. Both handle NULLs correctly, unlike NOT IN.
Example
LEFT JOIN order_items AS oi ON oi.product_id = p.id WHERE oi.product_id IS NULLRelated terms
- LEFT JOIN — A join that keeps every row of the left table and adds the matching right-hand rows, or NULLs when there is no match.
- IS NULL — The test for whether a value is NULL, which works where = NULL never does.
- EXISTS — An operator that is true when a subquery returns at least one row, whatever the row contains.
- IN — An operator that is true when a value equals any value in a list or in the rows a subquery returns.
Taught in
- What An Outer Join Really Returns — PostgreSQL, Indexing & Relational Data Modeling