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.
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
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.
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;
