Find records where join doesn't exist
postgresql, ruby-on-rails, sql
Solution
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 Ⓘ is `NULL`, while common logic would expect `TRUE` - and Ⓔ will return `TRUE`. 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
Problem
I have a scope to limit all `questions` by whether or not a user has voted on them. In the model: ``` scope :answered_by, lambda {|u| joins(:votes).where("votes.user_id = ?", u.id) } scope :unanswered_by, lambda {|u| joins(:votes).where("votes.user_id != ?", u.id) } ``` In the controller, I call them like this: ``` @answered = Question.answered_by(current_user) @unanswered = Question.unanswered_by(current_user) ``` The unanswered_by scope is incorrect. I essentially want to find where there is no vote. Instead, it is trying to look for if there is a vote that doesn't equal the current user. Any ideas how to return all records where a join doesn't exist?