Merge multiple table output but indicate which tables the records came from

Question

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not just a straight join. The tables have overlapping records (a common ID field between them), so my end result needs to have a field that indicates whether that row had a record from Table A only, from Table B only, or if it had records in both Tables A and B.

I am not a SQL guru (my queries are usually just selects with simple joins), so I’m having a hard time figuring this out. My current thought is to create three temp tables (one for A-only records, one for B-only records, and one for A+B records) then union them together for the output, but I’m sure there’s a better, more elegant/proper way to do this. Any thoughts on how this could be accomplished?

---

Answer 1

You can use a full outer join between your tables and check against null values to figure out where the data comes from.

select isnull(A.ID, B.ID) as ID,
       isnull(A.Name, B.Name) as Name,
       case
         when A.ID is null then 'B'
         when B.ID is null then 'A'
         else 'A+B'
       end as Tables   
from TableA as A
  full outer join TableB as B
    on A.ID = B.ID