CODEWITHSUNDEEP
sqlcoalesce
Doug Fir
Doug Fir

Reputation: 21212

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?

Upvotes: 0

Views: 266

Answers (2)

forpas
forpas

Reputation: 164089

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a full join. If your database supports booleans:

select id,
       ( t1.id is not null ) as in_t1,
       ( t2.id is not null ) as in_t2
from t1 full join
     t2
     using (id);

Otherwise, you case:

select id,
       (case when t1.id is not null then 1 else 0 end) as in_t1,
       (case when t2.id is not null then 1 else 0 end) as in_t2
from t1 full join
     t2
     using (id);

You can do something similar with union all and aggregation:

select id, max(in_t1) as in_t1, max(in_t2) as in_t2
from ((select id, 1 as in_t1, 0 as in_t2 from t1
      ) union all
      (select id, 0, 1
      )
     ) t
group by id;

Upvotes: 1

Related Questions