Mysql get all records described in in condition even if it does not exists in table

Question

Consider I have a table like the following:

my_table

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+

And say I have a query like this

select * from my_table where id in (1,2,3,4,5)

Is it possible to get output like the following,by changing the query.

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+
| 5   |  NULL   |
+---------------+

I tried using self JOIN and other conditions and also google'd a lot,but didn't find a solution.

Can anyone suggest a solution?

Answer 1

Unfortunately, mysql doesn't have a built in function that generates a series as many other databases do). There are (at least) two ways of doing it:

Hard code the desired values as a subquery, then left join to your table:

select x.id, t.name
from (select 1 id
  union select 2
  union select 3
  union select 4
  union select 5) x
left join my_table t on t.id = x.id

But this is tedious and hard to code and maintain.

Or (as I have done before) create a table (once) and populate with natural numbers (once) to use as a proxy series generator:

create table numbers (num int);
insert into numbers values (1), (2), (3), ... etc

then:

select n.num id, t.name
from numbers n
left join my_table t on t.id = n.num
where n.num in (1,2,3,4,5)

Once set up and populated with lots of numbers, this approach is very handy.

You can create a similar table populated with dates, used in a similar way, which is very handy for producing figures for every date in a range when not all dates have data.