MySQL LIMIT inside IN Operator

Question

select distinct column1 
from Table1 
where Table1id in ((select T2.Table1id 
                    from Table2 T2 
                    where (conditions) 
                    order by T2.column) 
                   limit 2
                  );

I cannot use limit inside the In operator. Do we have any other way to limit inside IN operator? Or do we have any other way without using IN and also without using any joins?

Error (while using limit inside the In Operator):-

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Change in to join https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu — Ergest Basha, Aug 22 '22 at 09:38
Why the parentheses that separate the limit clause from its query by the way? They don't seem to make sense. — Thorsten Kettner, Aug 22 '22 at 10:02

score 2 · Accepted Answer · edited Aug 23 '22 at 05:35

2

For non-specific MySQL versions, Ergest gave you a good solution of using JOIN. Here is another workaround in which an outer layer is used on top of the derived table.

select distinct column1 
from Table1 
where Table1id in (select id 
                   from 
                       (select  T2.Table1id as id
                        from Table2 T2 
                        where (conditions) 
                        order by T2.column 
                        limit 2) tb);

PS: this trick can be used to bypass the ERROR 1093 (HY000): You can't specify target table 'terms' for update in FROM clause

edited Aug 23 '22 at 05:35

marc_s

732,580
175
1,330
1,459

answered Aug 22 '22 at 09:53

blabla_bingo

1,825
1
2
5

Haha, it's so very much like MySQL to allow this workaround. It's the same with update and delete. It really makes me wonder why the restriction exists at all, when it is so easy to handle that. This should be really quick to apply within the MySQL sources, if only the developers would care, and everyone would be happy. – Thorsten Kettner Aug 22 '22 at 09:58

score 1 · Answer 2 · edited Aug 23 '22 at 05:35

1

What a weird restriction. Well, you can simply use an ad-hoc view (aka. WITH clause or CTE):

with limited as 
(
    select T2.Table1id
    from Table2 T2
    where (conditions)
    order by T2.column
    limit 2
)
select distinct column1
from Table1
where Table1id in (select Table1id from limited);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cc148fae3a1089324446ec792e1476e2

edited Aug 23 '22 at 05:35

marc_s

732,580
175
1,330
1,459

answered Aug 22 '22 at 09:41

Thorsten Kettner

89,309
7
49
73

Does the CTE improve the query performance ? Sometimes I just can't help thinking if folks like me who hang on to the old good 5.7 are missing more than just the convenience of window functions. – blabla_bingo Aug 22 '22 at 10:11
@blabla_bingo: No, that shouldn't influence run time. In this case it's just for readability. In other cases CTEs are used to avoid redundant code in a query or to run a recursive query. But yes, MySQL made a huge leap from versions 5 to 8, and everybody who can upgrade should do that in my opinion. – Thorsten Kettner Aug 22 '22 at 10:20

Rick James · Answer 3 · 2022-08-27T16:54:30.633

0

Let's turn it inside out:

select  distinct T1.column1
    FROM  
        ( SELECT  T2.Table1id
            from  Table2 T2
            where  (conditions)
            order by  T2.column
            limit  2
        ) AS x
    JOIN  Table1 AS T1  ON T1.table1id = x.Table1id

The inner (derived) table may need DISTINCT.

edited Aug 27 '22 at 16:54

answered Aug 27 '22 at 16:12

Rick James

135,179
13
127
222

MySQL LIMIT inside IN Operator

3 Answers3