Find Rows With Common Property

The table below relates users to groups. Which groups do two specified users have in common?

        ID    USER_ID   GROUP_ID
---------- ---------- ----------
         1          1          1
         2          2          1
         3          1          2
         4          3          2
         5          2          2

Here's a query that works in MS-Access and Oracle Express:

select group_id
from
  user_group where group_id in (select group_id from user_group where user_id = 1) 
  and user_id = 2;

  GROUP_ID
----------
         1
         2

Note: I've used where … in because a user can belong to more than one group. If the sub-query returns more than one row, Oracle Express reports this error:

ORA-01427: single-row subquery returns more than one row

You can look at the problem as one where you're looking for the intersection of two sets. If your DBMS supports it, you can use the intersect set operator. The following query works in Oracle Express:

select group_id from user_group where user_id = 1
intersect
select group_id from user_group where user_id = 2;

  GROUP_ID
----------
         1
         2