I need all entries from Table A and all entries from Table B that are NOT common to table A. I have 6 common fields. How do I go with this? I tried select … from Table A left join Table B, it missed some values from Table A. Tried also right join, full join…Any pointers on how to do this?
Advertisement
Answer
Hmmm . . . Is this what you want? Assuming that a
and b
have the same columns:
select a.* from a union all select b.* from b where not exists (select 1 from a where a.col1 = b.col1 and a.col2 = b.col2 and . . . );
EDIT:
SparkSQL should support not exists
, but you can write it using left join
instead:
select a.* from a union all select b.* from b left join a on a.col1 = b.col1 and a.col2 = b.col2 and . . . where a.col1 is null;