Find records where join doesn't exist Find records where join doesn't exist sql sql

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:

  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.


Your query can look like this:

SELECT *FROM   questions qWHERE  NOT EXISTS (    SELECT FROM votes v     WHERE  v.question_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:

try this and let me know if it works


scope :unanswered_questions, lambda { joins('LEFT OUTER JOIN votes ON = votes.question_id').where('votes.question_id IS NULL') }


scope :unanswered_by, lambda {|u| where(" NOT IN (SELECT votes.question_id from votes where votes.user_id = ?)", }

And if you want to do EXISTS query in elegant and Rails-ish manner, you can use Where Exists gem I've written:

Question.where_not_exists(:votes, user_id:

Of course, you can made scope of it as well:

scope :unanswered_by, ->(user){ where_not_exists(:votes, user_id: }