force a condition value not found to appear with the "IN" operator – mysql

November 18, 2022

how to make a value that does not exist in the Table used as search value, appear in the result as "null"?

In this example "roger" doesn’t exists on Table, but i need to force it too appear anyway.

SELECT `name`, `age` FROM `persons` WHERE `name` IN ('john','mike','jane','roger');

Table persons :

| name | age |
|------|-----|
| john | 20  |
| mike | 25  |
| jane | 31  |

Intended Result:

| name  | age   |
|-------|-------|
| john  | 20    |
| mike  | 25    |
| jane  | 31    |
| roger | null  |

>Solution :

One approach is to use left outer join like this:

select names.name, persons.age
from (select 'john' 
      union select 'mike'
      union select 'jane'
      union select 'roger') as names(name)
left outer join persons using (name);

Another approach that keeps the names together could use a trick with json_table:

select names.name, persons.age 
from json_table('["john", "mike", "jane", "roger"]', 
                '$[*]' columns (name text path '$')) names
left outer join persons using (name);