# How do you find all the values that match the keys in the set if the keys can be repeated?

• There's a sign. `Data`

``````| id | Key | Value  |
|----|-----|--------|
| 1  | 0   | f30467 |
| 2  | 1   | 406b67 |
| 3  | 2   | c029bd |
| 4  | 3   | 5c512f |
| 5  | 4   | 739fd6 |
| 6  | 5   | 096b71 |
| 7  | 6   | b1e8b2 |
| 8  | 7   | e71828 |
| 9  | 8   | 8d8566 |
| 10 | 9   | 94dcdc |
``````

Here is a sample of the necessary sample:

``````| Key | Value  |
|-----|--------|
| 0   | f30467 |
| 1   | 406b67 |
| 2   | c029bd |
| 2   | c029bd |
| 0   | f30467 |
``````

I've tried to use that request:

``````SELECT `Key`, `Value` FROM `Data` WHERE `Key` IN ('0', '1', '2', '2', '0')
``````

It does not give the right result:

``````| Key | Value  |
|-----|--------|
| 0   | f30467 |
| 1   | 406b67 |
| 2   | c029bd |
``````

Reference to example http://sqlfiddle.com/#!9/6bdbd/4 ♪

The next request gives the required result, but this is not the best solution for a large set of values. `Key`:

``````SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '0'
UNION ALL
SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '1'
UNION ALL
SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '2'
UNION ALL
SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '2'
UNION ALL
SELECT `Key`, `Value` FROM `Data` WHERE `Key` = '0'
``````

Reference to example http://sqlfiddle.com/#!9/6bdbd/4 ♪

How to get the results I need without using the design `UNION ALL`?

• `mysql` does not provide the necessary operators or `unnest`No `values` in the block `from`familiar `postgresql` and nothing else in return. Even the 5.7 JSON cannot be unpacked into a set of lines. So the only thing you can try with your request is to rewrite it in. `join`:

``````select `Key`, `Value`
from `Data`
join (
select 0 as `Key`
union all
select 1
union all
select 2
union all
select 2
union all
select 0
) targetkeys using(`Key`)
``````

But I doubt it'll be better than your request. And it doesn't seem to guarantee the order.

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2