COALESCE and NULLIF

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Display missing values and intentionally turn matching values into NULL.

Run the example

SELECT TOP (5) CustomerID,
       COALESCE(Email, N'[missing]') AS EmailDisplay
FROM dbo.Customers
ORDER BY CustomerID;

Read it step by step

1. Email is nullable in dbo.Customers.
2. COALESCE returns Email when present and the fallback text when it is NULL.
3. The original Email value is unchanged; ORDER BY fixes the five-row sample.

Check the result

The fallback is a display label, not a new email address stored in the table.

Practice

For Products 1 and 2, return NULL instead of ListPrice when the price equals 39.00.

SELECT ProductID, NULLIF(ListPrice, 39.00) AS PriceUnless39
FROM dbo.Products
WHERE ProductID IN (1, 2)
ORDER BY ProductID;