如何用SQL（Cross Apply）查询无任何关联用户邮箱的客户

To get customers where all their linked CustomerUsers have a NULL EmailAddress, we can use one of two efficient approaches. Let's break them down:

Method 1: Using NOT EXISTS #

This method checks that there are no users for a given customer who have a non-null email address. If no such user exists, the customer is included in the result.

SELECT c.CustomerId, c.SignUpDate, c.LastContactDate
FROM dbo.Customer c
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.CustomerUsers cu
    WHERE cu.CustomerId = c.CustomerId
      AND cu.EmailAddress IS NOT NULL
);

How it works: #

• For each customer in dbo.Customer, the subquery looks for any associated CustomerUsers record where EmailAddress is not NULL.
• If the subquery returns no rows (meaning all users have NULL emails), NOT EXISTS evaluates to true, and the customer is included.
• This is generally efficient because it stops searching as soon as it finds a non-null email for a customer (short-circuit evaluation).

Method 2: Using GROUP BY and HAVING #

This approach groups customers by their ID and checks that none of their users have a non-null email. We can use COUNT(EmailAddress) (which only counts non-null values) to verify this.

SELECT c.CustomerId, c.SignUpDate, c.LastContactDate
FROM dbo.Customer c
JOIN dbo.CustomerUsers cu ON c.CustomerId = cu.CustomerId
GROUP BY c.CustomerId, c.SignUpDate, c.LastContactDate
HAVING COUNT(cu.EmailAddress) = 0;

How it works: #

• We join the two tables to get all customer-user pairs.
• Grouping by customer ID allows us to aggregate the email addresses for each customer.
• COUNT(cu.EmailAddress) counts how many users have a non-null email. If this count is 0, all users have NULL emails, so the customer is included.

Note: #

If a customer has no associated CustomerUsers records at all, this method will exclude them (since the JOIN will drop those customers). If you want to include such customers, use a LEFT JOIN instead and adjust the HAVING clause to COUNT(cu.EmailAddress) = 0 AND COUNT(cu.UserId) > 0 (to exclude customers with no users) or just COUNT(cu.EmailAddress) = 0 (to include customers with no users, since their email count is 0).

Testing with Your Sample Data #

Using either method on your sample data will return:

CustomerId | SignUpDate | LastContactDate
456        | 30/12/2017 | 20/02/2018

Which is correct, since customer 456's users both have NULL emails, while customer 123 has one user with a valid email.

Fixing Your Existing Reverse Query #

Just as a side note, your existing reverse query (to find customers with at least one email) has a typo (WHERE AND). Here's the corrected version:

SELECT c.CustomerId, cu.EmailAddress
FROM dbo.Customer c
CROSS APPLY (
    SELECT TOP(1) EmailAddress 
    FROM dbo.CustomerUsers cu 
    WHERE cu.CustomerId = c.CustomerId 
    ORDER BY cu.[Primary] DESC
) cu;

内容的提问来源于stack exchange，提问作者Harambe