Understanding and Handling NULL Values in SQL

When working with SQL, one of the most confusing concepts for beginners is dealing with NULL values. NULL doesn’t represent zero or a blank entry (empty string)—it means unknown or missing. Understanding how NULL works is crucial for writing queries that behave as you expect.

What Is NULL?

In SQL, NULL represents the absence of a value. It’s a placeholder that tells the database, “We don’t know what this is yet,” or “This information wasn’t provided.”

For example, a customer record might include a first name and last name, but have a NULL value for the middle name. This doesn’t mean the customer doesn’t have a middle name. More likely, that information was never provided. Since the database doesn’t know the value, it stores it as NULL.

It’s also important to understand that multiple NULL values don’t imply the same missing information. If several customers have NULL middle names, that doesn’t mean they all share the same middle name — it just means the database has no value for any of them. Each NULL represents an unknown value, not a shared one.

Nullable and Not Nullable Fields

When defining a table, each column can be set as either:

  • NULL (nullable): The column can contain missing values.
  • NOT NULL (not nullable): The column must always have a value—think of it as required.

For example:

SQL
CREATE TABLE Sales.Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    MiddleName NVARCHAR(100) NULL,
    LastName NVARCHAR(100) NOT NULL,
    Phone NVARCHAR(20) NULL
);

In this table, every customer must have a FirstName and LastName, but MiddleName and Phone can be left blank (NULL).

When neither option is specified, the default behavior is to allow nulls. The statement below creates a table with the same properties as the statement shown above. However, when writing statements that will be used more than once it’s considered best practice to specify NULL or NOT NULL for each column to clearly indicate intention.

SQL
CREATE TABLE Sales.Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    MiddleName NVARCHAR(100),
    LastName NVARCHAR(100) NOT NULL,
    Phone NVARCHAR(20)
);

NULL Does Not Equal NULL

The tricky part is that NULL behaves differently from normal values.

Suppose the Sales.Customers table contains this data:

CustomerIDFirstNamePhone
1Alice555-1234
2BobNULL
3Carol555-6789

To find all records missing a phone number, you might try filtering using WHERE Phone = NULL. However, that query won’t return any results.

SQL
SELECT * 
FROM Sales.Customers 
WHERE Phone = NULL;

Returns no rows.

Instead, we need to use WHERE Phone IS NULL. This query will return all records where no value is stored in Phone.

SQL
SELECT * 
FROM Sales.Customers 
WHERE Phone IS NULL;

Output:

CustomerIDFirstNamePhone
2BobNULL

Likewise, we need to use WHERE Phone IS NOT NULL to return all records with a Phone value.

SQL
SELECT * 
FROM Sales.Customers 
WHERE Phone IS NOT NULL;

Output:

CustomerIDFirstNamePhone
1Alice555-1234
3Carol555-6789

Considerations When Using Aggregate Functions

When using aggregate functions such as COUNT, AVG, or SUM, it’s important to understand that most aggregates ignore NULL values by default. This behavior can affect your results in ways that aren’t always obvious at first.

Aggregate functions are designed to work with known values. When SQL encounters a NULL, it treats it as “unknown,” not as zero, empty, or false. Since math can’t be performed on unknown values, SQL simply leaves them out of the calculation.

This behavior helps prevent misleading results, but it also means your totals and averages may not reflect the full number of records in the table.

  • An average may seem higher than expected because rows with missing values were excluded
  • A sum may seem lower because NULL rows didn’t contribute anything
  • A count may not match the total row count

None of these results are “wrong” — they’re just based on how SQL treats missing data.

Consider the example below:

SQL
SELECT COUNT(Phone) AS [PhoneCount]
FROM Sales.Customers;

Output:

PhoneCount
2

Even though there are three rows in the table, only two of them contain a non-NULL Phone value. Because COUNT(column_name) counts values, not rows, any NULL values in that column are skipped.

Now compare that to:

SQL
SELECT COUNT(*) AS [PhoneCount]
FROM Sales.Customers;

Output:

PhoneCount
3

COUNT(*) counts rows, not column values, so it includes every row in the table regardless of whether any columns contain NULLs.

When working with aggregates, it helps to pause and ask:

  • Am I counting rows, or counting known values?
  • Do missing values matter for this calculation?
  • Am I measuring activity, totals, or data completeness?

The answers determine whether COUNT(*), COUNT(column_name), or additional NULL-handling logic makes the most sense.

Understanding how aggregates interact with NULL values is a key step toward writing queries that produce accurate, predictable results, especially as your datasets grow larger and more complex.

Database-Specific Considerations

While the concept of NULL is consistent across SQL platforms, the functions used to work with NULL values can vary depending on the database system you’re using. This is especially noticeable when you want to replace a NULL value with something more readable, such as a placeholder message in a report or query result.

Although these functions have different names, they all serve the same basic purpose: return the original value if it exists; otherwise, return a fallback value. The behavior is the same across platforms, only the syntax changes.

Don’t worry about memorizing every variation. What matters is recognizing when you need to handle NULL values and knowing that your database provides a function to do it. Once you recognize that pattern, switching between platforms becomes much easier.

You’ll also notice that COALESCE appears in multiple database systems and is part of the SQL standard, which makes it a good choice when writing queries that need to work across different platforms.

Below are a few common examples showing how different databases handle this same task.

SQL Server

SQL
SELECT ISNULL(Phone, 'No phone provided') AS [PhoneStatus]
FROM Sales.Customers;

MySQL

SQL
SELECT IFNULL(Phone, 'No phone provided') AS [PhoneStatus]
FROM Sales.Customers;

PostgreSQL

SQL
SELECT COALESCE(Phone, 'No phone provided') AS [PhoneStatus]
FROM Sales.Customers

Oracle

SQL
SELECT NVL(Phone, 'No phone provided') AS [PhoneStatus]
FROM Sales.Customers;

Output:

PhoneStatus
555-1234
No phone provided
555-6789

Key Takeaways

  • NULL means “unknown”, not zero or an empty value.
  • Decide upfront which columns should allow NULL based on whether the data is optional.
  • Use IS NULL and IS NOT NULL to test for missing values — not = or <>.
  • Remember that many aggregate functions (COUNT, AVG, SUM) ignore NULL values.
  • Use SQL-standard options like COALESCE when you want queries that work across databases.
  • Use database-specific functions like ISNULL, IFNULL, or NVL when you’re working in a particular system.
  • Be intentional with NULL handling so your queries return accurate, predictable results.

Understanding how SQL handles missing data makes it easier to trust your query results, explain them clearly, and build reports that reflect the real story in your data.

Related Posts

Scroll to Top