Return NULL for missing values in an IN list

Question

I have a table like this:

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

and a query like

 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 NULLs on missing ids, that is

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

I guess there should be a tricky LEFT JOIN with itself, but can't wrap my head around it.

EDIT: I see people are thinking I want to "fill the gaps" in a sequence, but actually what I want is to substitute NULL for the missing values from the IN list. For example, this

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

should return

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

Also, the order doesn't matter much.

EDIT2: Just adding a couple of related links:

• How to select multiple rows filled with constants?
• Is it possible to have a tableless select with multiple rows?

Answer 1

You could 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

Please see fiddle here.

Answer 2

Yes, you can. But that will be tricky since there are no sequences in MySQL.

I assume you want just any selection, so it's:

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 fiddle)

It will work as combination of powers of 2 to generate consecutive table of numbers. Your values are passed to WHERE clause, so you can substitute there any set of values.

I would recommend you to use application for this case - because it will be faster. It may have some sense if you want to use this row set somewhere else (i.e. in some other queries) - but if not, it's a work for your application.

If you'll need higher values, add more rows to sequence generator, like in this fiddle.