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