1

I want to get some rows repeated at my SQL result. I have tested IN operator but it would return each row one time only:

SELECT ID,text from myTable WHERE id IN (2,2,3,4,4,5)

It's can be done by sending multiple SELECT queries,

SELECT ID,text from myTable WHERE id=1
UNION ALL
SELECT ID,text from myTable WHERE id=4
UNION ALL
SELECT ID,text from myTable WHERE id=4
UNION ALL
SELECT ID,text from myTable WHERE id=4
UNION ALL
SELECT ID,text from myTable WHERE id=4
UNION ALL
SELECT ID,text from myTable WHERE id=4
UNION ALL
SELECT ID,text from myTable WHERE id=5

But it become very slow when I have big request.

alihardan
  • 183
  • 1
  • 14
  • look here: https://dba.stackexchange.com/questions/45512/how-do-i-select-items-from-a-table-where-a-single-column-must-contain-two-or-mo – BlooB Aug 21 '17 at 03:22
  • Possible duplicate of [Define variable to use with IN operator (T-SQL)](https://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql) – BlooB Aug 21 '17 at 03:35
  • No, it is different. I have edited my question. – alihardan Aug 21 '17 at 03:47
  • 1
    Just to pick up on "_It's working, but it sends many "select" request to database_". Remember that SQLite isn't a traditional client-server database, so "sending a request" is a mostly meaningless phrase. You'd have to benchmark to be certain, but I doubt the speed of the multi-`UNION` method will be much different than any other solution (although [CL's answer](https://stackoverflow.com/a/45791738/2096401) looks far neater). – TripeHound Aug 21 '17 at 08:01
  • using UNION method, you can't use More than 500 "select" command in sqlite! – alihardan Aug 21 '17 at 10:12
  • @AliHardan That 500 item restriction was removed from SQLite a long time ago. (So it should be possible in PHP in ten years or so …) – CL. Aug 21 '17 at 12:20
  • @CL. I mean 500 restriction in Compound SELECT, no 500 item restriction. the restriction is currently exists. I encountered this restriction yesterday! see https://sqlite.org/limits.html and find "SQLITE_MAX_COMPOUND_SELECT" in that page. – alihardan Aug 21 '17 at 20:58
  • Oops, you're right. That limit was removed only from VALUES. – CL. Aug 22 '17 at 08:42

1 Answers1

3

The IN operator just checks whether the value on the left matches some value on the right. To return multiple rows, you have to either use multiple SELECTs, or create a temporary table where the value 4 appears in multiple rows:

WITH IDs_to_search(ID) AS (
    VALUES (2), (3), (4), (4), (5)
)
SELECT ID, text
FROM MyTable
JOIN IDs_to_search USING (ID);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks, it faster than multiple SELECTs. – alihardan Aug 21 '17 at 10:10
  • Thanks a lot. I searched a lot and weren't good. It works good on SQLLite3 :XX this solution even save the order of results based on input IDs. Thanks :X – ali reza Apr 25 '22 at 04:27
  • @alireza The order is not guaranteed unless you use ORDER BY. (You can add a second column to the temporary table, if needed.) – CL. Apr 25 '22 at 06:10