I've a postgres table with data like

In my query I want all present by name, and if name doesn't exist return "null" instead of no row for that particular name

Eg query where Name in ('Jackie', 'Jade', 'Cha', 'Xi')

Should return

CodePudding user response：

To produce the desired rows, you need to join with a table or set of rows which has all those names.

You can do this by inserting the names into a temp table and joining on that, but in Postgres you can turn an array of names into a set of rows using unnest. Then left join with the table to return a row for every value in the array.

select attendances.*
from
  unnest(ARRAY['Jackie','Jade','Cha','Xi']) as names(name)
left join attendances on names.name = attendances.name;