SQL JOIN, all from t1 and all from t2 except what’s common to t1

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?

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;