Query "all of" across many-to-many relation

Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables.

User
----
id
name

Group
-----
id
name

UserGroup
---------
user_id
group_id

Now, I want to write a query selecting all users that are in all of some specified groups. e.g. Select * from users where the user is part of every one of "group1", "group2", and "group3".

With a Django ORM query, I'd do something like

users = (
    User.objects
    .filter(user_group__group_id=group1.id)
    .filter(user_group__group_id=group2.id)
    .filter(user_group__group_id=group2.id)
)

Which would produce a join for each call to .filter, e.g.

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
INNER JOIN user_group g2 ON g2.user_id = id
INNER JOIN user_group g3 ON g3.user_id = id
WHERE g1.group_id = %s
  AND g2.group_id = %s
  AND g3.group_id = %s

This becomes a bit hairy if I were to query a bigger set to match by.

So what is a better way to do this? If I were to ask for "any" rather that "all", if would be a simple matter of

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
WHERE g1.group_id in %s

But that is not what I need.

A small note: My specific environment is on Postgres, so no fancy MSSql thing will help me here. Preferably, the answer should be general enough to use in any SQL flavour.

You can do it with "fancy Postgres" features - much easier than "fancy MS SQL features" ;)

You can aggregate all the group IDs into an array and then compare that.

If with "all of" you mean those users that are assigned to exactly those groups, you can use something like this:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) = array[1,2,3];

Note that the = operator for arrays depends on the order [1,2,3] is a different array than [3,1,2] that's why array_agg() uses an order by and the values in the array are sorted as well.

If with "all of" you mean those users that are assigned to at least those groups (but could be assigned to more) then you can use a simple "contains" operator:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) @> array[1,2,3];

The "contains" operator @ does not depend on the order of the elements.

If you need to return the complete row from the users table, you can do the aggregation in a derived table and join to that:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    GROUP BY user_id    
    HAVING array_agg(group_id) @> array[1,2,3]
  ) ug on ug.user_id = u.id

The second query can also be done using standard SQL:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    WHERE group_id in (1,2,3)
    GROUP BY user_id    
    HAVING count(distinct group_id) = 3
  ) ug on ug.user_id = u.id;

This solution has the disadvantages that you need to synchronize the values for the IN list and the count(..) = 3 expressions when you change the list of IDs

StackExchange Database Administrators Q#235471, answer score: 8