Find records where join doesn't exist
Use an EXISTS
expression:
WHERE NOT EXISTS (
SELECT FROM votes v -- SELECT list can be empty
WHERE v.some_id = base_table.some_id
AND v.user_id = ?
)
The difference
... between NOT EXISTS()
(Ⓔ) and NOT IN()
(Ⓘ) is twofold:
Performance
Ⓔ is generally faster. It stops processing the subquery as soon as the first match is found. The manual:
The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.
Ⓘ can also be optimized by the query planner, but to a lesser extent since
NULL
handling makes it more complex.Correctness
If one of the resulting values in the subquery expression is
NULL
, the result of Ⓘ isNULL
, while common logic would expectTRUE
- and Ⓔ will returnTRUE
. The manual:If all the per-row results are either unequal or null, with at least one null, then the result of
NOT IN
is null.
Essentially, (NOT) EXISTS
is the better choice in most cases.
Example
Your query can look like this:
SELECT *
FROM questions q
WHERE NOT EXISTS (
SELECT FROM votes v
WHERE v.question_id = q.id
AND v.user_id = ?
);
Do not join to votes
in the base query. That would void the effort.
Besides NOT EXISTS
and NOT IN
there are additional syntax options with LEFT JOIN / IS NULL
and EXCEPT
. See:
- Select rows which are not present in other table