CODEWITHSUNDEEP
sql
alihardan
alihardan

Reputation: 350

How get a row multiple times repeated at SQL query result?

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.

Upvotes: 1

Views: 578

Answers (1)

CL.
CL.

Reputation: 180070

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

Upvotes: 3

Related Questions