Non-matching records in T-SQL One-to-Many Relations

I was recently tasked with finding all the people in our CRM software that lack email addresses, and with around 3000 contacts, there was no way I was going to do it by hand. SQL to the rescue!
There was just one issue, email addresses are stored in a different table to allow each person to have multiple addresses on file. After a bit of digging around, I came up with this:

SELECT
    T.FirstName,
    T.LastName,
    T.CompanyName
FROM (
    SELECT
        CRM.dbo.Companies.CompanyName,
        CRM.dbo.People.FirstName,
        CRM.dbo.People.LastName,
        CRM.dbo.Emails.EmailAddress
    FROM CRM.dbo.People
    LEFT JOIN CRM.dbo.Emails
    ON CRM.dbo.Emails.PersonId = CRM.dbo.People.PersonId
    LEFT JOIN CRM.dbo.Companies
    ON CRM.dbo.Companies.CompanyId = CRM.dbo.People.CompanyId
) AS T
WHERE T.EmailAddress IS NULL

Let’s go through this line-by-line so we can see exactly what it does.

SELECT

We’re looking to get data out of the SQL server, so select is the way to go.

   T.FirstName,
    T.LastName,
    T.CompanyName

This is the three fields of actual data we want back from the query.

FROM (

this outer select will fetch data out of the LEFT JOIN that we’re about to construct.

   SELECT
        CRM.dbo.Companies.CompanyName,
        CRM.dbo.People.FirstName,
        CRM.dbo.People.LastName,
        CRM.dbo.Emails.EmailAddress

These are the three fields that the left join will output. We need the email address here so that we can check if it is set to NULL in the outer select statement.

FROM CRM.dbo.People

The select will start with the data from the People table

LEFT JOIN CRM.dbo.Emails

Then we fetch additional data from the Emails table

ON CRM.dbo.Emails.PersonId = CRM.dbo.People.PersonId

To make sure the rows are matched correctly, we compare the PersonID column of eachrow

LEFT JOIN CRM.dbo.Companies

Let’s get some more external data, from the Companies table this time

ON CRM.dbo.Companies.CompanyId = CRM.dbo.People.CompanyId

This time, we’re looking at the CompanyID, so that we can get the correct company name for the results

) AS T

Save that sub-query as T

WHERE T.EmailAddress IS NULL

For each row in the sub-query, check if the EmailAddress is null

And that leaves us with this:

FirstName	LastName	CompanyName
System	User	NULL
Joe	Bloggs	Acme Ltd
John	Smith	HyperGlobalSoft
Jane	Smith	Consoto PLC