[SOLVED] NOT EXISTS Vs. Left Outer Join

NOT EXISTS Vs. Left Outer Join

I am confused by the results that I am getting via the NOT EXISTS vs. LEFT OUTER JOIN with NULL.

Consider the below 2 queries which produce very different results. Please note that the fkMasterPersonID is NOT a foreign key to the right table (tblInternetMasterPerson)

Query 1

SELECT tl.pkLeadID, tl.fkMasterPersonID
FROM dbo.tblPhoneLead tl
WHERE NOT EXISTS (
    SELECT MasterPersonID
    FROM dbo.tblInternetMasterPerson
)

The above returns no results

Query 2

SELECT tl.pkLeadID, tl.fkMasterPersonID
FROM dbo.tblPhoneLead tl
LEFT JOIN dbo.tblInternetMasterPerson mp
    ON tl.fkMasterPersonID = mp.MasterPersonID
WHERE tl.fkMasterPersonID IS null

The above returns 237 records where the fkMasterPersonID are all NULL.

What would be the correct way of determining through NOT EXISTS whether the fkMasterPersonID DOES NOT exist on the dbo.tblInternetMasterPerson? This table does have the column pkMasterPersonID but it is auto incremented and is not a foreign key to any other table.

Solution

You need to correlate the exists subquery to the outer query. Here is one way:

SELECT tl.pkLeadID, tl.fkMasterPersonID
FROM dbo.tblPhoneLead tl
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.tblInternetMasterPerson mp
    WHERE mp.MasterPersonID = tl.fkMasterPersonID
);