MySQL — LIMIT inside IN/ALL/ANY/SOME subqueries

Contents

  1. 1. Option 1 — wrap in a derived table
  2. 2. Option 2 — join two derived tables

Put LIMIT in a subquery that sits under IN, ALL, ANY, or SOME, and MySQL hits you with:

ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

LIMIT is already a weak spot in MySQL, so the server blocks this combination. You still need the result set sometimes.

Example rules:

  • 5 first-prize winners
  • 15 second-prize winners
  • Anyone who won first prize cannot win second

You might write something like:

1
2
3
4
5
6
7
8
SELECT second_prize_user_id FROM t
WHERE <second_prize_conditions>
AND user_id NOT IN (
SELECT first_prize_user_id FROM t
WHERE <first_prize_conditions>
LIMIT 5
)
LIMIT 15;

That inner LIMIT is exactly what MySQL forbids. Two workarounds I have used:

Option 1 — wrap in a derived table

1
2
3
4
5
6
7
8
9
10
SELECT second_prize_user_id FROM t
WHERE <second_prize_conditions>
AND user_id NOT IN (
SELECT * FROM (
SELECT first_prize_user_id FROM t
WHERE <first_prize_conditions>
LIMIT 5
) AS t
)
LIMIT 15;

Option 2 — join two derived tables

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM (
SELECT second_prize_user_id FROM t
WHERE <second_prize_conditions>
) AS t1,
(
SELECT first_prize_user_id FROM t
WHERE <first_prize_conditions>
LIMIT 5
) AS t2
WHERE t1.user_id <> t2.user_id
LIMIT 15;

EXPLAIN on both looked the same to me — filesort and a full table scan. Option 1 is easier to write.

Questions? Leave a comment below or email: newbvirgil@gmail.com