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

duplicates, mysql, where-in

Solution

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.

Problem

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?

Original source