SQL Server

Understanding NULL in SQL Server

Learn what NULL means in SQL Server, how IS NULL and IS NOT NULL work, why = NULL fails, and how COALESCE and COUNT handle missing data.

By JaviPublished 10 min readBeginner

A missing email is different from an empty email address. In SQL Server, NULL represents a value that is missing or unknown. It is not zero, false, an empty string, or the four letters “NULL”. This lesson builds on ORDER BY sorting and WHERE filters in SQL Server Foundations, then prepares you to combine customer and order data in the next lesson.

Find missing values

Use IS NULL to find customers without an email. The sample returns Isla Brooks:

SQL query: Find missing valuesT-SQL
SELECT CustomerId, FirstName, LastName, Email
FROM dbo.Customers
WHERE Email IS NULL;
T-SQL3 linesRead-only query display. It is not connected to a database.

This SSMS capture uses dbo.Orders in JaviBuildsLab and finds the row whose Amount is NULL.

In the SSMS capture, the lab checks its nullable Orders.Amount column:

sql SELECT OrderId, CustomerId, OrderDate, Status, Amount, Region, ProductId FROM dbo.Orders WHERE Amount IS NULL;

SQL Server query testing Amount IS NULL in dbo.Orders with one matching row
IS NULL finds the order whose Amount has no value; this result contains one matching row.

For dbo.Customers, IS NOT NULL finds the other nine customers:

SQL query: Find missing values (2)T-SQL
SELECT CustomerId, FirstName, LastName, Email
FROM dbo.Customers
WHERE Email IS NOT NULL;
T-SQL3 linesRead-only query display. It is not connected to a database.
  1. Known value Miami
  2. Empty string
  3. NULL unknown or missing
  4. IS NULL selects missing emails
NULL is not an empty string

These states differ. An empty string is a known value containing zero characters. NULL says no value is available or known. It is not zero, false, or the text NULL.

This difference can affect real business rules. A blank email might mean that someone deliberately cleared the field, while NULL might mean it has never been collected. If both cases need different treatment, do not convert one into the other just to make a report look tidy. First decide what each state means to the application and people using the data. SQL Server stores a NULL marker separately from ordinary character values, so a test for the empty string will not find NULL rows, and IS NULL will not find empty strings.

Why equals NULL does not work

This is incorrect for testing a missing value:

-- Incorrect: comparison with NULL is unknown, not true
WHERE Email = NULL

A comparison involving NULL is generally unknown, not true or false. SQL Server uses this third outcome alongside true and false. WHERE keeps only rows where its condition is true, so unknown rows do not pass. Use IS NULL, not = NULL.

The same reasoning affects other conditions. Email <> 'ana.torres@example.com' does not include Isla: SQL Server cannot tell whether her unknown email differs from Ana’s. Add OR Email IS NULL if the goal includes missing addresses.

This is why a filter may return fewer rows than a reader expects. WHERE State <> 'FL' sounds like “everything except Florida,” but it keeps only rows for which the comparison is true. A NULL state is neither known to equal FL nor known to differ from FL, so the predicate is unknown and the row is filtered out. If “not Florida, including an unknown state” is the intended rule, write both parts: WHERE State <> 'FL' OR State IS NULL. When filtering several nullable columns, check each condition against missing values so the result matches the question being asked.

NULL in expressions and COALESCE

In SQL Server, concatenating a string with NULL generally yields NULL under modern CONCAT_NULL_YIELDS_NULL behavior. For example, adding a missing middle name into a full-name expression can make the expression NULL. Choose an explicit fallback when that is the intended presentation.

COALESCE returns the first non-NULL expression:

SQL query: NULL in expressions and COALESCET-SQL
SELECT FirstName, LastName,
       COALESCE(Email, 'No email') AS EmailDisplay
FROM dbo.Customers;
T-SQL3 linesRead-only query display. It is not connected to a database.

Isla displays “No email”; the other stored addresses remain. This changes only the query result, not the value stored in the table. Use a fallback that is clear to the reader and appropriate for the column’s type.

NULL and counts

COUNT(*) counts rows, whether or not a column is NULL. COUNT(Email) counts only non-NULL email values. In this dataset the first count is 10 and the second is 9:

SQL query: NULL and countsT-SQL
SELECT COUNT(*) AS CustomerRows,
       COUNT(Email) AS CustomersWithEmail
FROM dbo.Customers;
T-SQL3 linesRead-only query display. It is not connected to a database.

This distinction matters whenever a nullable column is used to measure completeness.

Other aggregates generally ignore NULL inputs too. SUM(Email) is not meaningful because Email is text, but for a numeric nullable column SUM(Amount) adds known amounts and skips unknown ones. That can be useful, but it does not mean an unknown amount should be interpreted as zero. COUNT(*) answers “how many rows are here?”; COUNT(Email) answers “how many rows have a known email?” Those are different questions and should receive different column names in a report.

Common mistakes and practice

Avoid = NULL, treating NULL as blank, replacing missing data before deciding what it means, assuming COUNT(column) equals COUNT(*), and forgetting nullable columns in filters. WHERE State <> 'FL' also excludes NULL states because the comparison is unknown. Include them explicitly when needed.

Avoid replacing NULL with a made-up value too early. A display label such as “No email” is helpful for a person, but if that replacement is used before filtering or grouping it can mix real text with a placeholder and obscure which records are truly missing. Keep the stored value and its meaning intact, then choose a fallback at the point where a human-readable output is needed. Check nullable columns in the table definition or documentation instead of guessing that every row has a value.

Practice: (1) find missing emails; (2) find present emails; (3) display a fallback email label; (4) count rows and known emails; (5) find non-Florida customers while retaining unknown states.

Practice answers

SQL query: Practice answersT-SQL
-- 1
SELECT CustomerId FROM dbo.Customers WHERE Email IS NULL;
-- 2
SELECT CustomerId FROM dbo.Customers WHERE Email IS NOT NULL;
-- 3
SELECT COALESCE(Email, 'No email') AS EmailDisplay FROM dbo.Customers;
-- 4
SELECT COUNT(*) AS Rows, COUNT(Email) AS KnownEmails FROM dbo.Customers;
-- 5 (State is NOT NULL for every current demo customer)
SELECT CustomerId FROM dbo.Customers WHERE State <> 'FL' OR State IS NULL;
T-SQL10 linesRead-only query display. It is not connected to a database.

Next, combine Customers and Orders with JOIN.

Check what you learned

2 quick questions

Question 1 of 2How do you test whether a value is missing (NULL)?
Question 2 of 2What does NULL mean in a column?

SQL Server Foundations

Lesson 8 of 25

Continue to next lesson

SQL JOINs Explained in SQL Server

Continue to Lesson 9

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL