Joining a table with a list

Question

I have a list of values like this:

("UXT8","U61J","U61W","U62U","X82U","U5NF","U635","U526","28FX")

I would like to be able to extract from a table how many times each one of them occurs in a field of a table, even when the number of occurences is 0.

What I am looking is similar to

select key_field, count(*) from table
where key_field in ("UXT8","U61J","U61W","U62U","X82U","U5NF","U635","U526","28FX")
group by key_field;

but with a kind of left join effect.

Is this possible in sql, and specifically in its sqlite or mysql variants?

Thanks

Answer 1

I would use a union to build a memory table instead of the list:

select t.code, count(mytable.unique_id) from 
  (
  select 'UXT8' as code union 
  select 'U61J' as code union 
  select 'U61W' as code union 
  select 'U62U' as code union 
  select 'X82U' as code union 
  select 'U5NF' as code union 
  select 'U635' as code union 
  select 'U526' as code union 
  select '28FX' as code
  ) as t 
 left outer join mytable on t.code = mytable.key_field
 group by t.code
 order by t.code;