IS NULL and missing values

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. Some Customers records have no email address. SQL Server stores that absence as NULL, which needs its own predicate.

Run the example

SELECT CustomerID, FirstName
FROM dbo.Customers
WHERE Email IS NULL
ORDER BY CustomerID;

Read it step by step

1. WHERE Email IS NULL selects rows with a missing Email value. IS NOT NULL would select customers who have an email.
2. Email = NULL does not work as an equality test: its result is UNKNOWN, not TRUE. WHERE keeps only TRUE rows.
3. ORDER BY CustomerID displays the four missing-email customers in stable ID order.

Check the result

The result does not display an Email column because it was not selected, but every returned row has Email IS NULL. NULL is different from an empty string: an empty string is a known text value with zero characters.

Practice

Find orders with no assigned sales employee. How many should the unmodified v1.0.0 dataset contain?

SELECT COUNT(*) AS UnassignedOrders FROM dbo.Orders WHERE SalesEmployeeID IS NULL;