Selecting with subqueries in MySQL (Subqueries with ANY, and IN)

Question

Thanks for the great answers!

For More Information

• More info on MySQL IN comparison operator
• Joining Tables
• Subqueries with ANY, IN, and SOME

---

This is hard to explain, so lets set the stage...

userActions         userGroupMap
+------+--------+   +------+-------+
| user | action |   | user | group |
+------+--------+   +------+-------+
| x    | acted! |   | x    | a     |
| y    | acted! |   | y    | a     |
| y    | acted! |   | z    | b     |
| z    | acted! |   +------+-------+
| y    | acted! |
| z    | acted! |
| x    | acted! |
| z    | acted! |
+------+--------+

I want to select group a's actions. My idea was to

SELECT actions, user FROM userActions
    WHERE user = (SELECT user, group FROM userGroupMap WHERE group = a)

But obviously this subquery returns more than one row. Should I use a JOIN?

Subquery returns more than 1 row

Answer 1

One approach is this:

SELECT actions,
       user
FROM   userActions
WHERE  user IN
               (SELECT user
               FROM    userGroupMap
               WHERE   [group] = 'a'
               );

However, with large tables, this query tends to be inefficient and doing a join is better:

SELECT actions,
       userActions.user
FROM   userActions
       INNER JOIN
              (SELECT user
              FROM    userGroupMap
              WHERE   [group] = 'a'
              ) AS tmp
       ON     userActions.user = tmp.user;

Alternatively, as Jonathon mentioned, you could have done this and its pretty much as efficient, if not more:

SELECT actions,
       userActions.user
FROM   userActions
       INNER JOIN userGroupMap
       ON     userActions.user = userGroupMap.user
WHERE  [group] = 'a';