I have two datasets: A and B
Dataset A:
indiv_id January 1000068000004 1 1000068000011 1 1000068000012 1 Dataset B:
indiv_id March 1000068000003 1 1000068000011 1 1000068000015 1 Desired Result:
Indiv_id January March 1000068000003 null 1 1000068000004 1 null 1000068000011 1 1 1000068000012 1 null 1000068000015 null 1 How do I join the tables so that I have all of the Indiv_IDs from BOTH lists and their values in the column that is distinct to their particular dataset?
Thanks
You can use full join:
select indiv_id, a.january, b.march from a full join b using (indiv_id); The using clause makes this particularly convenient because you don't need to worry about any coalesce()s in the select.
USING tidbit is a nice bonus. I'd never realized that.You've got 3 cases to deal with: the ID is in both datasets, the ID is only in dataset A, or the ID is only in dataset B. You can't solve that with select ... from dataset_A left join dataset_B ... because you'll get the first two cases but not the third.
What you need is to define a view that gives you all the IDs across both datasets, then left join to each dataset:
with all_IDs as (select indiv_ID from dataset_A union select indiv_ID from dataset_B) select indiv_ID, january, march from all_IDs left join dataset_A using (indiv_ID) left join dataset_B using (indiv_ID) 