where column in from another select results with limit (mysql/mariadb)

Question

when i run this query returns all rows that their id exist in select from table2

SELECT * FROM table1 WHERE id in (
    SELECT id FROM table2 where name ='aaa'
)

but when i add limit or between to second select :

SELECT * FROM table1 WHERE id in (
    SELECT id FROM table2 where name ='aaa' limit 4
)

returns this error :

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

This is because *This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'* What version are you using ? — Cid, Dec 29 '19 at 11:46
Read [this](https://mariadb.com/kb/en/subquery-limitations/#limit) — Cid, Dec 29 '19 at 11:47
i cant put limit in other select because other select in my project is a left join query and there are many duplicate ids , for example when i limit 10 it returns 8 or 1 or 3 or 9 rows from left table and 10 rows from right table — mmjvox, Dec 29 '19 at 11:59
no i need to return some ids from table2 with limit or between 2 numbers to put theme in last select ---> "where in ('some ids from table2')" — mmjvox, Dec 29 '19 at 12:07
when i use this works fine `SELECT * FROM job_forum WHERE id in ( SELECT id FROM job_forum WHERE id>0 and id<10 )` but according to [https://mariadb.com/kb/en/subquery-limitations/#limit] this type of limit not supported what is the best solution? — mmjvox, Dec 29 '19 at 12:09

Gordon Linoff · Accepted Answer · 2019-12-29T13:44:55.780

You are using LIMIT without an ORDER BY. This is generally not recommended because that returns an arbitrary set of rows -- and those can change from one execution to another.

You can convert this to a JOIN -- fortunately. If id is not duplicated in table2:

SELECT t1.*
FROM table1 t1 JOIN
     (SELECT t2.id
      FROM table2 t2
      WHERE t2.name = 'aaa' 
      LIMIT 4
     ) t2
     USING (id);

If id can be duplicated in table2, then:

SELECT t1.*
FROM table1 t1 JOIN
     (SELECT DISTINCT t2.id
      FROM table2 t2
      WHERE t2.name = 'aaa' 
      LIMIT 4
     ) t2
     USING (id);

Another fun way uses LIMIT:

SELECT t1.*
FROM table1 t1
WHERE id <= ANY (SELECT t2.id
                 FROM table2 
                 WHERE t2.name = 'aaa'
                 ORDER BY t2.id
                 LIMIT 1 OFFSET 3
                );

LIMIT is allowed in a scalar subquery.

tanks `SELECT t1.* FROM table1 t1 JOIN (SELECT DISTINCT t2.id FROM table2 t2 WHERE t2.name = 'aaa' LIMIT 4 ) t2 USING (id); ` worked — mmjvox, Jan 01 '20 at 19:29

Barbaros Özhan · Answer 2 · 2019-12-29T12:53:37.280

You can use an analytic function such as ROW_NUMBER() in order to return one row from the subquery. I suppose, this way no problem would occur like raising too many rows issue :

SELECT * FROM
(
 SELECT t1.*,
       ROW_NUMBER() OVER (ORDER BY t2.id DESC) AS rn
  FROM table1 t1
  JOIN table2 t2 ON t2.id = t1.id
 WHERE t2.name ='aaa'
) t
WHERE rn = 1

P.S.: Btw, id columns are expected to be primary keys of your tables, aren't they ?

Update ( depending on your need in the comment ) Consider using :

SELECT * FROM
(
 SELECT j.*,
       ROW_NUMBER() OVER (ORDER BY j.id DESC) AS rn2
  FROM job_forum j
  CROSS JOIN 
      ( SELECT t.*,
               ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY t2.id DESC) AS rn1 
          FROM table2 t2
         WHERE t2.name ='aaa'
           AND t2.id = j.id ) t2 
  WHERE rn1 = 1
) jj
WHERE rn2 <= 10

`SELECT * FROM table1 WHERE id in ( SELECT id FROM table2 WHERE id>(SELECT MAX(id) FROM table2 )-11 and id<(SELECT MAX(id) FROM table2 )-0 )` this is what i need but it uses two more selects — mmjvox, Dec 29 '19 at 12:35
tanks your answer is true but that day i was very angry and tired , today i tested it — mmjvox, Jan 01 '20 at 19:32

where column in from another select results with limit (mysql/mariadb)

2 Answers2