Search code examples
sqlcoalesce

coalesce join where seeking to return id if it exists in either table

Suppose I have two tables, t1 and t2 and they both have a single field 'id'.

I'd like to run a query that returns:

id|in t1|in t2

Where fields in t1 and in t2 are booleans.

So the rows would be the distinct ids then two fields denoting whether the id exists in t1, t2 or both.

I've previously encountered syntax that used coalesce on a join and i think this is a appropriate time to go with that approach but I cannot remember exactly how to structure it?

Solution

  • You can use a full join:

    select
  coalesce(t1.id, t2.id) id,
  case when t1.id is not null then 'true' else 'false' end in_t1,
  case when t2.id is not null then 'true' else 'false' end in_t2
from t1 full outer join t2
on t2.id = t1.id