How to get multiple rows if where clause has repeated condition values in SQL

Question

I am trying to fetch all the rows using WHERE IN (...ids) format.This is working fine. But if ids inside IN are repeated only one row for that ID is returned by default by SQL.

Is there a way i can get same row N times if the ID is present N times inWHERE IN (...ids)?

Ex: Data in Table

id	name
1	AAA
2	BBB
3	CCC

Query SELECT * FROM people WHERE id IN (1, 2, 2, 3);

Result

id	name
1	AAA
2	BBB
3	CCC

Expected Result

id	name
1	AAA
2	BBB
2	BBB
3	CCC

Is there a way to get this result in SQL?

I am using Postgres.

Thank you

| 3 | CCC |

Gordon Linoff · Accepted Answer · 2021-06-13 14:28:12Z

1

WHERE clauses do not -- as a general rule -- change the number of rows. Instead, use JOIN:

SELECT p.*
FROM (VALUES (1), (2), (2), (3)) v(id) JOIN
     people p
     USING (id);

Note: If you want all ids in the list, even those not in people use LEFT JOIN.

If you want to pass in the values as a parameter, then arrays are more convenient than VALUES():

SELECT p.*
FROM UNNEST(ARRAY[1, 2, 2, 3]) v(id) JOIN
     people p
     USING (id);

edited Jun 13, 2021 at 14:28

answered Jun 13, 2021 at 14:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get multiple rows if where clause has repeated condition values in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related