PostgreSQL where all in array

by

Assuming the join table follows good practice and has a unique compound key defined, i.e. a constraint to prevent duplicate rows, then something like the following simple query should do.

select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2

It’s important to note that the number 2 at the end is the length of the list of user_ids. That obviously needs to change if the user_id list changes length. If you can’t assume your join table doesn’t contain duplicates, change “count(*)” to “count(distinct user_id)” at some possible cost in performance.

This query finds all conversations that include all the specified users even if the conversation also includes additional users.

If you want only conversations with exactly the specified set of users, one approach is to use a nested subquery in the where clause as below. Note, first and last lines are the same as the original query, only the middle two lines are new.

select conversation_id from conversations_users where user_id in (1, 2)
   and conversation_id not in
   (select conversation_id from conversations_users where user_id not in (1,2))
group by conversation_id having count(*) = 2

Equivalently, you can use a set difference operator if your database supports it. Here is an example in Oracle syntax. (For Postgres or DB2, change the keyword “minus” to “except.)

select conversation_id from conversations_users where user_id in (1, 2)
  group by conversation_id having count(*) = 2
minus
  select conversation_id from conversations_users where user_id not in (1,2)

A good query optimizer should treat the last two variations identically, but check with your particular database to be sure. For example, the Oracle 11GR2 query plan sorts the two sets of conversation ids before applying the minus operator, but skips the sort step for the last query. So either query plan could be faster depending on multiple factors such as the number of rows, cores, cache, indices etc.

More Related Contents:

  1. SQL where joined set must contain all values but may contain more
  2. Rails 4 LIKE query – ActiveRecord adds quotes
  3. How to use ANY instead of IN in a WHERE clause?
  4. PostgreSQL GROUP BY different from MySQL?
  5. Best way to count rows by arbitrary time intervals
  6. ActiveRecord Arel OR condition
  7. Perform this hours of operation query in PostgreSQL
  8. Join multiple tables with active records
  9. Find records where join doesn’t exist
  10. Find all records which have a count of an association greater than zero
  11. Rails find record with zero has_many records associated [duplicate]
  12. Prepared Statement on Postgresql in Rails
  13. ActiveRecord find_each combined with limit and order
  14. Any downsides of using data type “text” for storing strings?
  15. Query for array elements inside JSON type
  16. Calculating Cumulative Sum in PostgreSQL
  17. Is it possible to make a recursive SQL query?
  18. MySQL not using indexes with WHERE IN clause?
  19. Way to try multiple SELECTs till a result is available?
  20. Disable Rails SQL logging in console
  21. PostgreSQL IF statement
  22. Split given string and prepare case statement
  23. Get top row(s) with highest value, with ties
  24. PostgreSQL wildcard LIKE for any of a list of words
  25. Query that ignore the spaces
  26. GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
  27. Postgres function returning table not returning data in columns
  28. COPY with dynamic file name
  29. PostgreSQL column ‘foo’ does not exist
  30. PostgreSQL: top n entries per item in same table

Leave a Comment