Union of two tables with information of the origin of each row

Question

suppose I have two tables A and B, both with only 1 column name. Some rows appear in both tables while some are exclusive to one table. I want to union these two tables together into a table C that has 3 columns name, is_in_A and is_in_B. For a row that appeared in both A and B, the last 2 fields would both be 'true'. For a row that appeared in only A, only is_in_A would be 'true' and is_in_B would be false.

My current solution works like this: Create three temporary tables that all have the columns name, is_in_A and is_in_B:

• A_join_B that has all rows that are in A and B. is_in_A and is_in_B are both true for every row in this table
• A_minus_B that has all rows that are exclusive to A. is_in_A is true and is_in_B is false for every row in that table.
• B_minus_A respectively.

Now I union all 3 tables together to get my result.

Is there a better/easier way to do this (ideally as a single union without any temporary tables)?

Answer 1

You can do it with a FULL join of the tables:

SELECT COALESCE(a.name, b.name) AS name,
       CASE WHEN a.name IS NULL THEN false ELSE true END AS is_in_A,
       CASE WHEN b.name IS NULL THEN false ELSE true END AS is_in_B
FROM a FULL OUTER JOIN b
ON b.name = a.name

If your database does not support boolean values like true and false, change to 1 and 0 or string literals 'true' and 'false'.

See a simplified demo.

Answer 2

You can use union all and aggregation:

select name, sum(in_a), sum(in_b)
from ((select name, 1 as in_a, 0 as in_b
       from a
      ) union all
      (select name, 0, 1
       from b
      )
     ) ab
group by name;