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

Question

Thanks for the great answers!

For More Information

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

achinda99 · Accepted Answer · 2009-03-05T14:02:29.597

3

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';

edited Mar 05 '09 at 14:02

answered Mar 04 '09 at 21:01

achinda99

5,020
4
34
42

Thank you! With your answer I got my results flawlessly. I also appreciate the note about large tables (Which is why I accepted your answer). Can you elaborate on why you put group in brackets-"[group]"? – Blaine Mar 04 '09 at 22:44
There is no obvious reason why this needs a sub-query; a straight inner-join with filtering WHERE clause should also do the job. – Jonathan Leffler Mar 04 '09 at 23:52
@Blaine: I put [group] in brackets because its a keyword and some DBMS freak out if keywords used as column/table names is not explicitly stated. Same reason I'd put [table] in brackets. @Jonathan Leffler: You are right, I think I was just distracted by the question and it using subqueries. – achinda99 Mar 05 '09 at 13:48

score 1 · Answer 2 · answered Mar 04 '09 at 20:50

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

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

(Amended: only the user column should be returned, as noted by others.)

score 1 · Answer 3 · edited Mar 04 '09 at 23:53

1

Couldn't you just do something like:

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

edited Mar 04 '09 at 23:53

Jonathan Leffler

730,956
141
904
1,278

answered Mar 04 '09 at 20:51

snnkmtt

11
1

Yes; this works best in this situation. But there is also the issue of 'how to deal with sub-queries that return multiple rows', and that is done with IN or = ANY or variants thereof. But those queries can often be rewritten with a join instead of a sub-query. – Jonathan Leffler Mar 04 '09 at 23:55

score 1 · Answer 4 · answered Mar 04 '09 at 20:56

1

Actually, this query will give you what you need:

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

answered Mar 04 '09 at 20:56

Nathan DeWitt

6,511
8
46
66

score 0 · Answer 5 · edited Mar 04 '09 at 23:56

0

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

The subquery was returning user and group (two fields) when it should be returning just user.

edited Mar 04 '09 at 23:56

Jonathan Leffler

730,956
141
904
1,278

answered Mar 04 '09 at 20:49

qpingu

950
7
11

I agree that the sub-query was wrong in part because of the multiple columns; however, the question was about it returning more than one row, and your version still does that - and therefore fails at runtime. – Jonathan Leffler Mar 04 '09 at 23:57

Glavić · Answer 6 · 2009-03-04T21:05:55.607

0

Rather use join than subquery:

SELECT
    userActions.action,
    userActions.user
FROM
    userActions
CROSS JOIN userGroupMap ON 
    userGroupMap.user = userActions.user AND
    userGroupMap.group = 'a'

edited Mar 04 '09 at 21:05

answered Mar 04 '09 at 21:00

Glavić

42,781
13
77
107

On large tables, this will dramatically increase the size of the temporary table before cutting it down with group. Its less efficient than joining on a subset of data. – achinda99 Mar 04 '09 at 21:04
What if put where statment in join ? Will that be better ? – Glavić Mar 04 '09 at 21:06
No need for CROSS JOIN; use INNER JOIN (or just plain JOIN). – Jonathan Leffler Mar 04 '09 at 23:55
In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise. – Glavić Mar 05 '09 at 05:40

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

6 Answers6