user1032531 user1032531 - 5 months ago 13
SQL Question

Returing all records including duplicated with MySQL IN clause

Assuming both

fk
equal to 1 and 3 exists in the table, the following query will return two records.

SELECT fk, name FROM my_table WHERE fk IN (1,3,1);


How can I instead return three records:

fk name
1 john
3 mary
1 john

Answer

You need to use a join instead:

select t.fk, t.name
from my_table t join
     (select 1 as x union all select 3 union all select 1
     ) vals
     on t.fk = vals.x;

If you also want to keep the ordering, then use a second column -- the order of the rows after the join is not guaranteed unless you include an order by:

select t.fk, t.name
from my_table t join
     (select 1 as x, 1 as ordering union all
      select 3, 2 union all
      select 1, 3
     ) vals
     on t.fk = vals.x
order by vals.ordering;