Haytem BrB Haytem BrB - 1 month ago 6
SQL Question

Check for non matching rows in an outer join query with multiple keys

I have two tables A and B, and a query like:

Select * from A left join B
on A.key1=B.Key1
and A.key2=B.Key2


Can I identify non matching rows based on Key1 OR Key2 using only one join query?

I know I can use:

Select * from A left join B
on A.key1=B.Key1
and A.key2=B.Key2
where B.Key1 is null or B.Key2 is null


But it does only tell me that the couple Key1/Key2 from table A is not present in table B, but doesn't tell me which Key exactly is not present in Table B.

Thanks!

Answer

You need full outer join

Select * 
from A 
full outer join B on A.key1=B.Key1
                 and A.key2=B.Key2
where B.Key1 is null or B.Key2 is null 
   or A.Key1 is null or A.Key2 is null 
Comments