SQL NULL Values: IS NULL, IS NOT NULL and COALESCE Explained

SQL Tutorial · Chapter 13 of 32

SQL NULL values represent missing or unknown data. NULL is not zero, not an empty string and not FALSE: it is the absence of a value. Because NULL is unknown, ordinary comparisons such as = NULL never return TRUE. You test for it with IS NULL and IS NOT NULL, and you replace it with a default using COALESCE.

SQL NULL Syntax

SELECT columns FROM table_name WHERE column IS NULL;
SELECT columns FROM table_name WHERE column IS NOT NULL;
SELECT COALESCE(column, 'default') FROM table_name;

Sample Table

For this chapter the Orders table gains a ShipDate column. Orders that have not shipped yet have no ship date, which is exactly what NULL is for.

Example 1: Find Missing Values with IS NULL

SELECT OrderID, Product
FROM Orders
WHERE ShipDate IS NULL;

These are the unshipped orders. Writing WHERE ShipDate = NULL instead returns zero rows in every database, because the comparison evaluates to unknown.

Example 2: Find Present Values with IS NOT NULL

SELECT OrderID, ShipDate
FROM Orders
WHERE ShipDate IS NOT NULL;

Example 3: Replace NULL with COALESCE

COALESCE returns the first non-NULL argument. It is the standard way to show a friendly label instead of a blank.

SELECT OrderID, COALESCE(ShipDate, 'Not shipped') AS ShipStatus
FROM Orders;

In a strictly typed database such as PostgreSQL or SQL Server, cast the date to text first: COALESCE(CAST(ShipDate AS VARCHAR), 'Not shipped').

Example 4: NULL in Arithmetic

Any calculation involving NULL produces NULL. Here we compute shipping days; unshipped orders come out as NULL rather than a number.

SELECT OrderID, julianday(ShipDate) - julianday(OrderDate) AS DaysToShip
FROM Orders;   -- SQLite syntax; see dialect notes

Example 5: NULL and Aggregate Functions

COUNT(column), SUM, AVG, MIN and MAX all skip NULLs. Only COUNT(*) counts every row.

SELECT COUNT(*) AS AllOrders, COUNT(ShipDate) AS Shipped
FROM Orders;

Three-Valued Logic

SQL conditions can be TRUE, FALSE or UNKNOWN. A WHERE clause keeps only TRUE rows, so an UNKNOWN result behaves like FALSE. This is why NOT IN with a NULL in the list returns nothing, and why Amount <> 100 silently drops rows where Amount is NULL.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• IS NULL, IS NOT NULL and COALESCE are standard in all four.
• Shortcuts: MySQL IFNULL(a, b), SQL Server ISNULL(a, b), Oracle NVL(a, b), SQLite IFNULL(a, b). COALESCE is the portable choice.
• Date arithmetic differs: MySQL DATEDIFF(ShipDate, OrderDate), SQL Server DATEDIFF(day, OrderDate, ShipDate), PostgreSQL ShipDate - OrderDate.
• Empty string vs NULL: Oracle treats ” as NULL; the four databases here do not.

Designing for NULL

Allow NULL only where a value can genuinely be unknown, such as a ship date before shipping, and declare columns NOT NULL everywhere else. Fewer nullable columns mean fewer IS NULL checks, fewer COALESCE wrappers and fewer surprising empty results from NOT IN. When a default makes sense, such as 0 for a quantity, set it in the table definition rather than storing NULL.

Common Mistakes

• Writing = NULL or <> NULL; always use IS NULL / IS NOT NULL.
• Expecting AVG to treat NULL as zero. It ignores the row entirely, which changes the denominator.
• Concatenating text with a NULL column and getting NULL for the whole string (except in SQL Server with CONCAT).
• Storing 0 or ‘N/A’ instead of NULL and then averaging the fake values.

Practice Exercise

1. Count how many orders have not shipped.
2. Show OrderID and ShipDate, displaying ‘Pending’ where the ship date is missing.

-- 1
SELECT COUNT(*) AS Unshipped FROM Orders WHERE ShipDate IS NULL;
-- 3

-- 2
SELECT OrderID, COALESCE(ShipDate, 'Pending') AS ShipDate FROM Orders;

Related Chapters

• SQL WHERE – the clause where IS NULL lives
• SQL COUNT, AVG, SUM – how aggregates skip NULL
• SQL LEFT JOIN – where NULLs appear for unmatched rows
• SQL from Scratch: full course overview

FAQ

What is a NULL value in SQL?

NULL means a value is missing or unknown. It is a marker, not a value, so it is different from 0, an empty string or FALSE.

Why does WHERE column = NULL return no rows?

Comparing anything with NULL gives UNKNOWN, not TRUE, and WHERE keeps only TRUE rows. Use IS NULL instead.

What is the difference between COALESCE and ISNULL?

COALESCE is standard SQL, accepts any number of arguments and works in every database. ISNULL (SQL Server) and IFNULL (MySQL, SQLite) take exactly two arguments and are vendor specific.

Chapter 13 of 32 · SQL from Scratch: all 32 chapters