Can a MySQL "IN" clause return duplicates?

user_2069516 0
Viewed 265

I realize most of the time you don't want this, but I'm looking for a way to get duplicate rows using IN.

SELECT `id`, `name`
FROM `users`
WHERE `id` IN
      (11, 11, 3, 11, 6)

My desired result:

+----+---------+
| id | name    |
+----+---------+
| 11 | Alice   |
| 11 | Alice   |
| 3  | Bob     |
| 11 | Alice   |
| 6  | Charlie |
+----+---------+

This is a really simplified example, but in the real query, being able to do this with IN would make my life about 1000 times easier.

mysql
3 Answers

No, it can't.

You're asking for all the rows, existing in a table, that meet some criteria (or, in this case, one criterion: the ID must be any of $thisListOfIds).

You're not asking to perform any duplication operations on the resulting set of rows.

More than that, I question whether this, even in your real-world complex use case, is actually the right way to go. You don't usually synthesize more redundant data when you perform queries, even for intermediary steps.

user_43862780

What you're looking for is a join, which produces the desired cartesian product for you:

SELECT users.id, users.name
FROM users
JOIN (
  SELECT 11 id, 1 ordinal UNION ALL
  SELECT 11 id, 2 ordinal UNION ALL
  SELECT 3  id, 3 ordinal UNION ALL
  SELECT 11 id, 4 ordinal UNION ALL
  SELECT 6  id, 5 ordinal
) t ON t.id = users.id
ORDER BY ordinal

Ordering is optional, of course... db-fiddle here

user_5217990

Same idea as @Lukas Eder with a bit more conscise syntax:

WITH t(id, ordinal) AS (
 VALUES ROW(11, 1), ROW(11,2), ROW(3,3), ROW(11,4), ROW(6,5)
)
SELECT users.id, users.name
FROM users
JOIN t ON t.id = users.id
ORDER BY ordinal;

db<>fiddle demo

user_50708790
Related