Find records where join doesn’t exist

by

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:

More Related Contents:

  1. PostgreSQL GROUP BY different from MySQL?
  2. Best way to count rows by arbitrary time intervals
  3. Perform this hours of operation query in PostgreSQL
  4. PostgreSQL where all in array
  5. SQL where joined set must contain all values but may contain more
  6. Rails 4 LIKE query – ActiveRecord adds quotes
  7. Prepared Statement on Postgresql in Rails
  8. Optimize GROUP BY query to retrieve latest row per user
  9. Computed / calculated / virtual / derived columns in PostgreSQL
  10. Check if value exists in Postgres array
  11. What is the difference between single quotes and double quotes in PostgreSQL?
  12. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  13. PostgreSQL sequence based on another column
  14. Finding similar strings with PostgreSQL quickly
  15. Reference alias in WHERE clause
  16. Sort NULL values to the end of a table
  17. How to compare the current row with next and previous row in PostgreSQL?
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. Continuing a transaction after primary key violation error
  20. Select (retrieve) all records from multiple schemas using Postgres
  21. Optimize groupwise maximum query
  22. Use email address as primary key?
  23. Join multiple tables with active records
  24. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  25. SQL query to find record with ID not in another table
  26. Get execution time of PostgreSQL query
  27. Postgresql tables exists, but getting “relation does not exist” when querying
  28. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  29. Referencing current row in FILTER clause of window function
  30. Is there a postgres CLOSEST operator?

Leave a Comment