Geek Answers Handbook

Returns NULL for missing values ​​in the IN list

I have a table like this:

id | val --------- 1 | abc 2 | def 5 | xyz 6 | foo 8 | bar

and type request

  SELECT id, val FROM tab WHERE id IN (1,2,3,4,5)

which returns

  id | val --------- 1 | abc 2 | def 5 | xyz

Is there a way to make it return NULL on missing identifiers, i.e.

  id | val --------- 1 | abc 2 | def 3 | NULL 4 | NULL 5 | xyz

I suppose there should be a complicated LEFT JOIN with me, but I can't wrap my head around it.

EDIT: I see that people think that I want to “fill in the blanks” in the sequence, but actually I want to replace NULL with the missing values ​​from the IN list. For example, this

  SELECT id, val FROM tab WHERE id IN (1,100,8,200)

must return

  id | val --------- 1 | abc 100 | NULL 8 | bar 200 | NULL

In addition, order is not a big deal.

EDIT2: just add a couple of related links:

+6
source share
2 answers

You can use this trick:

 SELECT v.id, t.val FROM (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) v LEFT JOIN tab t ON v.id = t.id

See the fiddle here .

+1
source

Yes, you can. But it will be difficult, since there are no sequences in MySQL.

I assume that you want only any choice, so it is:

 SELECT * FROM (SELECT (two_1.id + two_2.id + two_4.id + two_8.id + two_16.id) AS id FROM (SELECT 0 AS id UNION ALL SELECT 1 AS id) AS two_1 CROSS JOIN (SELECT 0 id UNION ALL SELECT 2 id) AS two_2 CROSS JOIN (SELECT 0 id UNION ALL SELECT 4 id) AS two_4 CROSS JOIN (SELECT 0 id UNION ALL SELECT 8 id) AS two_8 CROSS JOIN (SELECT 0 id UNION ALL SELECT 16 id) AS two_16 ) AS sequence LEFT JOIN t ON sequence.id=t.id WHERE sequence.id IN (1,2,3,4,5);

(check the script)

It will work as a combination of powers 2 to create a sequential table of numbers. Your values ​​are passed to WHERE , so you can replace any set of values.

I would recommend you use the application for this case - because it will be faster. This may make sense if you want to use this line elsewhere (for example, in some other queries), but if not, this will work for your application.

If you need higher values, add more lines to the sequence generator, as in this fiddle .

+1
source

More articles:


All Articles