SQL IN statement - keep duplicates when returning results

Question

I have an sql query something like this:

SELECT * FROM prices WHERE item IN ('item1', 'item2', 'item2', 'item3');

Notice the duplicate value 'item2'. I want the query to return the value for that item as many times as it appears in the IN clause. By default it only returns once. How can I do that?

Answer 1

Then you need to use left join:

select p.*
from (select 'item1' as item union all select 'item2' union all
      select 'item2' union all select 'item3'
     ) i left join
     prices p
     on i.item = p.item;

Answer 2

SQL treats items of the IN list as a set, ignoring the duplicates.

In databases that support table-valued parameters (TVP) you could replace IN with an inner join on a TVP, but MySQL does not offer them.

An alternative approach would be to populate a temporary table with the items from your IN list, and query with an inner join against it:

CREATE TEMPORARY TABLE InList (item varchar(10)); 
INSERT INTO InList(item) VALUES ('item1');
INSERT INTO InList(item) VALUES ('item2');
INSERT INTO InList(item) VALUES ('item2');
INSERT INTO InList(item) VALUES ('item3');

Now your query would look like this:

SELECT p.*
FROM prices p
INNER JOIN InList i ON i.item = p.item

Since joining produces a Cartesian product, rows of price would be repeated as many times as they are listed in the temporary InList table.