sql - Find records where join doesn't exist

Question

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?

Answer 1

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:

1. 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.

2. 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