PostgreSQL select IN with duplicate-CodePudding

I have a field of product variant names.

'XS', 'S', 'M', 'S', 'M', 'S', 'M', 'XS', 'S', 'M', 'XS', 'S', 'M', 'L', 'XS', 'XS', 'XS', 'S', 'XL', 'XS', 'M', 'XS', 'S', 'M', 'XS', 'M', 'S', 'XS', 'M', 'Chick Black', 'Brave Blue', 'Bold Red', 'Posh Pink', 'Purple Yum', 'Blue', 'Good Green', 'Orange', 'Purple Yum', 'Red', 'Yellow', 'Red', 'Blue', 'Purple Yum', 'Good Green', 'Orange', 'Pink', 'Light Blue', 'Red', 'Yellow', 'Green Elipse', 'Light Blue', 'Pink', 'Orange', 'White', 'Green Elipse', 'Pink Elipse', 'Blue Elipse', 'Red', 'Yellow Elipse', 'Green Elipse', 'Blue Elipse', 'Pink Elipse', 'Orange', 'White', 'Blue', 'Mint Green', 'Dusty Pink', 'Airforce Blue', 'Claret', 'Pink', 'Rubine Red', 'Green MM', 'Grey', 'Blue', 'Orange', 'Yellow', 'Light Blue', 'Pink', 'Rubine Red', 'Green MM', 'Grey', 'Blue', 'Orange', 'Yellow', 'Light Blue', 'SA 10', 'SA 12', 'SA 14', 'SA 16', 'Blue Babyhood', 'Pink Babyhood'

I want to select id from the variants table based on the list above. But when I do this query,

SELECT id FROM variants WHERE name IN ('XL', 'S', 'M', 'S', ...)

it returns grouped id. For example,

|id|name|
|0001|XL|
|0002|S|
|0003|M|
|0004|XS|

What I expect is a non-grouped result, so variants will be retrieved many times. For example, variant 'S' must be retrieved 2 times here.

|id|name|
|0001|XL|
|0002|S|
|0003|M|
|0002|S|
..etc

Is this possible to do? I hope my question is understandable. Thanks in advance!

CodePudding user response：

You can left join to an ad hoc derived table of the variant names. (Or inner join, if you don't want rows in the result for names that don't exist in variants.)

SELECT v.id,
       v.name
       FROM (VALUES ('XL'),
                    ('S'),
                    ('M'),
                    ('S'),
                    ...) n
                         (name)
            LEFT JOIN variants v
                      ON v.name = n.name;