Find diff between three tables

I’m trying to find the difference between three tables, a, b, and c. I’d like to join all three, showing nulls where each table does not have records.

Each table has two columns that I would like to join on, lets say first and last name.

So far, this is what I’m doing.

Select * from a 
FULL OUTER JOIN b on 
  a.firstName = b.firstName AND a.lastname = b.lastname 
FULL OUTER JOIN c on 
  (c.firstName = a.firstName and c.lastName = a.LastNAME) OR 
  (c.firstName = b.firstName AND c.lastname = b.LastName)

I’m not confident that the logic in my output is correct. Is there a better way of doing this? In english, the first join joins A and B, including records in each table that don’t exist in the other table. The second join joins A+B to C, joining C to either A or B if it matches.

Answer

You may want:

select firstname, lastname, sum(in_a), sum(in_b), sum(in_c)
from ((select firstname, lastname, 1 as in_a, 0 as in_b, 0 as in_c
       from a
      ) union all
      (select firstname, lastname, 0 as in_a, 1 as in_b, 0 as in_c
       from b
      ) union all
      (select firstname, lastname, 0 as in_a, 0 as in_b, 1 as in_c
       from c
      )
     ) abc
group by firstname, lastname;

This should each firstname/lastname pair with information about how many times the pair is in each table.

I prefer the union all/group by approach for this over left join for several reasons:

1. Without the using clause, the on clause gets a bit tricky as you add more tables.
2. Minimal use of coalesce().
3. Better handles duplicates within each table.
4. Handles NULL values for the keys.