SQL NULL Values

In SQL, NULL values represent missing or unknown data. A NULL is not zero and not an empty string; it means "no value". In our sample data, customer 10 (Jade Imports) has no ContactName, so that field is NULL.

IS NULL

You cannot test for NULL with =. Use IS NULL.

SELECT CustomerID, CustomerName
FROM Customers
WHERE ContactName IS NULL;

CustomerID | CustomerName
-----------+-------------
10         | Jade Imports
(1 row)

IS NOT NULL

IS NOT NULL returns rows that have a value.

SELECT COUNT(*) AS with_contact
FROM Customers
WHERE ContactName IS NOT NULL;

with_contact
------------
9
(1 row)

Why = NULL Does Not Work

Any comparison with NULL produces NULL (unknown), and WHERE only keeps rows where the condition is true. So this query returns no rows at all.

SELECT CustomerName
FROM Customers
WHERE ContactName = NULL;

Query ran successfully. No rows returned.

Replacing NULL with COALESCE

COALESCE returns the first non-NULL argument. It is standard SQL and works everywhere. MySQL and SQLite also have IFNULL, SQL Server has ISNULL, and Oracle has NVL.

SELECT CustomerName,
       COALESCE(ContactName, 'No contact') AS Contact
FROM Customers
WHERE Country = 'Canada' OR Country = 'Spain';

CustomerName   | Contact
---------------+-------------
Delta Supplies | Maria Garcia
Jade Imports   | No contact
(2 rows)

NULL in Calculations and Aggregates

• Arithmetic with NULL gives NULL: 5 + NULL is NULL
• COUNT(column) skips NULLs, COUNT(*) counts every row
• SUM, AVG, MIN and MAX ignore NULLs

Frequently asked questions

What is the difference between NULL and an empty string?

An empty string '' is a known value with zero characters. NULL means the value is unknown or missing. (Oracle is an exception: it treats empty strings as NULL.)

How do I replace NULL with 0 in SQL?

Use COALESCE(column, 0). It works in every major database.

Does COUNT include NULL values?

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only rows where that column is not NULL.