Find Missing records that are Not null

Question

I have two tables of account information. The 1st table T1 has descriptions of all product lines along with submission number, name, region, status, policynumber and few more. The second table T2 has only the information associated only for Property line along with its submission number, name region, status, policy number and others. Then column names are differently named in both the tables.

I need to compare the two tables and find the missing policy numbers of the Property line in the second table T2. I tried the below code and I get the list, yet I see the null values. How should I eliminate these null values from the result?

SELECT a.[Master Policy Number]
FROM   DB1.dbo.Global_Submission_Log AS a
WHERE 
    a.[Master Policy Number] NOT IN (
        SELECT b.PolNo
        FROM   DB1.dbo.PROPERTY_Files AS b
        WHERE  b.PolNo = a.[Master Policy Number]
            AND a.[Master Policy Number] IS NOT NULL
    )

Answer 1

You can move the is not null to outside the subquery. You already have an = condition in there that will exclude the nulls.

Select a.[Master Policy Number]
From   DB1.dbo.Global_Submission_Log a
Where  a.[Master Policy Number] Not In (Select b.PolNo
                                        From   DB1.dbo.PROPERTY_Files b
                                        Where  b.PolNo = a.[Master Policy Number])
And a.[Master Policy Number] Is Not Null

Answer 2

If you want to filter out NULL values, then that filter should go in the outer query, not in the inner query:

Select a.[Master Policy Number]
From   DB1.dbo.Global_Submission_Log a
Where  a.[Master Policy Number] Is Not Null and
       a.[Master Policy Number] Not In (Select b.PolNo
                                        From DB1.dbo.PROPERTY_Files b
                                        Where  b.PolNo = a.[Master Policy Number]
                                       );

Oh, this is very strange. You are comparing the same columns in for the NOT IN that are using in the correlation clause. For that reason and the semantics of NOT IN, you should just use NOT EXISTS instead.

I should note that NOT IN with a subquery is dangerous. If b.PolNo is ever NULL, then the NOT IN will never return true. My recommendation is to use NOT EXISTS instead:

Select gsl.[Master Policy Number]
From DB1.dbo.Global_Submission_Log gsl
Where gsl.[Master Policy Number] Is Not Null and
      not exists (select 1
                  from DB1.dbo.PROPERTY_Files pf
                  where pf.PolNo = gsl.[Master Policy Number]
                 );

Note that I also think queries are easier to follow if the table aliases are abbreviations for the tables themselves.

Answer 3

Select a.[Master Policy Number]
From   DB1.dbo.Global_Submission_Log a
Where  a.[Master Policy Number] Not In (Select b.PolNo
From   DB1.dbo.PROPERTY_Files b
Where  b.PolNo = a.[Master Policy Number]
) 
/* filter nulls on main query */ And a.[Master Policy Number] Is Not Null

Answer 4

If your result is the one that shows you the list of null, you could simply use delete from to delete the result.

delete from DB1.dbo.Global_Submission_Log Where [Master Policy Number] in ( Select a.[Master Policy Number]From DB1.dbo.Global_Submission_Log a Where a.[Master Policy Number] Not In (Select b.PolNo From DB1.dbo.PROPERTY_Files b Where b.PolNo = a.[Master Policy Number] And a.[Master Policy Number] Is Not Null) )

Answer 5

Did you try left join ?

Select T1.[Master Policy Number] 
From  DB1.dbo.Global_Submission_Log T1 
LEFT JOIN DB1.dbo.PROPERTY_Files T2 
ON T1.[Master Policy Number] on T2.PolNo 
Where  b.PolNo IS NULL

this will provide you the policy numbers that are not present in the Property_Files table but in Global_Submission table