Databricks, comparing two tables to see which records are missing

Question

I'm looking into two tables that are supposed to be equal. I run this query to see which records are missing in table B against table A (we have a 3-columned key):

select * 
from tableA A 
left join TableB B 
   on A.joinField1 = B.joinField1 
  and A.joinField2 = B.joinField2 
  and A.joinField3 = B.joinField3 
where B.joinField1 is null 
   or B.joinField2 is null 
   or B.joinField3 is null

This way, should a record in A be missing in B, it gets filtered in this query (based on the key). For some reason, when I randomly pick one of these missing records and look it up directly in table B (with a simple select, filtered on the key), it shows up. Why does my query include them when actually there is a match? there are no null values and fields formats match.

Karthikeyan Rasipalay Durairaj · Accepted Answer · 2021-12-15T18:34:45.157

3

We can use EXCEPT command for this . EXCEPT and EXCEPT ALL return the rows that are found in one relation but not the other. EXCEPT (alternatively, EXCEPT DISTINCT) takes only distinct rows while EXCEPT ALL does not remove duplicates from the result rows. Note that MINUS is an alias for EXCEPT. You can ref link

edited Dec 15 '21 at 18:34

answered Dec 15 '21 at 16:57

Karthikeyan Rasipalay Durairaj

1,920
13
35

Except works fine. Still wondering why my query doesn't. – Riccardo Lamera Dec 15 '21 at 17:50
what error you are getting ? – Karthikeyan Rasipalay Durairaj Dec 15 '21 at 17:57
1

another simple method , select data from tblA where data not in (select data from tblB) – Karthikeyan Rasipalay Durairaj Dec 15 '21 at 17:59
I don't get any error, those missing records aren't actually missing, but get still included in the query – Riccardo Lamera Dec 15 '21 at 18:02
In your query , You are joining based on the joinField1, joinField2, joinField3 conditions and where condition also 'is null' with all fields . In that case , if joining condition satisfied , where condition will not be satisfied at all . So is null condition , we should check other than the joining condition columns . – Karthikeyan Rasipalay Durairaj Dec 15 '21 at 18:32

Databricks, comparing two tables to see which records are missing

1 Answers1