Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Question

This might not be very sensible, but I'ld like to let MySQL return me the exact duplicate rows if there are duplicate criteria in the WHERE IN clause. Is this possible?

Take this example:

SELECT
   columns
FROM
   table
WHERE
   id IN( 1, 2, 3, 4, 5, 1, 2, 5, 5)

I'ld like MySQL to return me rows with id 5 three times, id's 1 and 2 twice, and 3 and 4 once.

As the lenght of the IN arguments, as well as the duplicate count (once, twice, three times, etc.), will be arbitrary I don't want to rely on UNION or JOIN. Is something like this possible otherwise?

Answer 1

I'm not sure why you want to ban JOIN as its fairly essential to SQL. It's like banning function calls in a functional language.

A good way to solve this is to create a result set containing the ids you want to return and join with it. Here's one way to do it:

SELECT Table1.*
FROM Table1
JOIN (SELECT 1 AS id
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 5
      UNION ALL SELECT 5) AS T1
ON Table1.id = T1.id

I'm not sure if you have considered this method? It has none of the problems that you seem to be afraid of.

If you ban joins you can't do this unless you use a stored procedure, which I'd say is worse than joining.