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 NULL

Related terms

Taught in

References