mysql - Select where field in values (if field in value not exist return as null) -

i want select data in clause. have $values: 1,2,3,4

and want select rows table in values.

  select `date` another_table id in (1,2,3,4) 

if another_table have rows id 1,2 , 3 only, mean id of 4 not exist. want id of 4 still selected return of null or nol or never.

      another_table      -------+-------------+      |  id  |  date       |      ----------------------              1     yesterday          2      today          3      tomorow          5      today 

the expected result be

     -------+-------------+      |  id  |  date       |      ----------------------              1     yesterday          2      today          3      tomorow          4      never 

how this?

thanks in advance

as 1 option use inline view rowsource, , perform outer join operation. can use if expression check whether matching row returned another_table, know id value not null if there matching row, join predicate (in on clause) guarantees that.)

as example:

select n.id      , if(a.id null,'never',a.date) `date`   ( select 1 id          union select 2          union select 3          union select 4        ) n   left   join another_table     on a.id = n.id  order n.id 

the inline view query gets executed, , results materialized temporary table (mysql calls derived table). when outer query runs, n table containing 4 rows.

obviously, if list of id values has change, you'd need change view definition. sql text inline view can generated dynamically array in programming language array.

for large number of values, inline view becomes unwieldy, , you'd better performance table, rather view.