Handling NULL Values in SQL

Walk into any record store and you'll find a shelf of vinyl waiting to be catalogued. Some sleeves have no price sticker. Some have no year stamped on the label. One has clearly been played a hundred times but nobody wrote down its condition. The store still owns these records. The data about them is just missing.

That's what a NULL is in SQL: not zero, not an empty string, not "we checked and there's nothing there." It means "we don't know." And if you don't handle that gap on purpose, your queries will handle it for you, usually in ways you didn't ask for.

Here's the database behind our example. A small shop called The Vinyl Vault keeps its stock in one table:

CREATE TABLE records (
    record_id      INT PRIMARY KEY,
    title           VARCHAR(100),
    artist          VARCHAR(100),
    pressing_year   INT,
    condition_grade VARCHAR(20),
    list_price      DECIMAL(6,2),
    sale_price      DECIMAL(6,2),
    units_in_stock  INT
);

The owner enters stock with an INSERT statement like this one, leaving a cell out entirely whenever that detail isn't known yet:

INSERT INTO records
    (record_id, title, artist, pressing_year, condition_grade, list_price, sale_price, units_in_stock)
VALUES
    (1, 'Blue Train', 'John Coltrane', 1957, 'VG+', 45.00, NULL, 3),
    (2, 'Songs in the Key of Life', 'Stevie Wonder', NULL, 'NM', 60.00, 48.00, 1),
    (3, 'Unknown Pleasures', 'Joy Division', 1979, NULL, 30.00, NULL, 0),
    (4, 'Blue', 'Joni Mitchell', 1971, 'VG', NULL, 22.00, 2),
    (5, 'Kind of Blue', 'Miles Davis', 1959, 'NM', 55.00, NULL, 4);

Notice that NULL here is a keyword, not a quoted string. Write NULL on its own, never 'NULL'. Quoting it would insert the four-character text "NULL" into the column, which is a value like any other and breaks every one of the checks below. Once that statement runs, the table looks like this:

record_id	title	artist	pressing_year	condition_grade	list_price	sale_price	units_in_stock
1	Blue Train	John Coltrane	1957	VG+	45.00	NULL	3
2	Songs in the Key of Life	Stevie Wonder	NULL	NM	60.00	48.00	1
3	Unknown Pleasures	Joy Division	1979	NULL	30.00	NULL	0
4	Blue	Joni Mitchell	1971	VG	NULL	22.00	2
5	Kind of Blue	Miles Davis	1959	NM	55.00	NULL	4

Four columns here have gaps: a missing pressing year, a missing condition grade, a missing list price, a missing sale price. Each gap tells a different story. Maybe the owner hasn't graded the sleeve yet. Maybe a record isn't on sale. Maybe nobody has looked up its original pressing date. SQL doesn't know or care why the value is missing, and that's exactly the trap.

Why this matters

Try to find every record with no listed price using the instinct most people bring from everyday logic:

SELECT title FROM records WHERE list_price = NULL;

This returns nothing. Not an error, not a warning. NULL isn't a value you can compare with =, because comparing "unknown" to anything, even to another unknown, produces "unknown". SQL treats an unknown result the same as false when deciding whether to include a row. The query runs, the shop owner sees an empty result set, and walks away thinking every record has a price while it doesn't.

Aggregate functions carry the same trap. Count the shop's inventory:

SELECT COUNT(*) AS total_records, COUNT(sale_price) AS records_on_sale
FROM records;

Output:
| total_records | records_on_sale |
|---|---|
| 5 | 2 |

COUNT(*) counts rows. COUNT(sale_price) counts only the rows where that column has an actual value as NULLs get skipped. Both numbers are correct for what they measure, but if you meant to ask "how many records are on sale" and wrote COUNT(*) by habit, you'd report five instead of two. Averages have the same quirk: AVG(sale_price) divides by the number of non-NULL rows, not by every row in the table. Miss that, and a small sale section can look like it's pulling in more revenue per item than it really is.

None of this is a bug. It's SQL being consistent about what "I don't know" means. The job is to decide, column by column, what should happen when the value isn't there and that's where a handful of functions earn their keep.

The toolkit

COALESCE: the standard fallback

COALESCE takes a list of values and returns the first one that isn't NULL. It works the same way across MySQL, PostgreSQL, SQL Server, SQLite, and Oracle, which makes it the one worth learning first.

Say the shop wants a clean price list for a printed flyer. A record on sale should show its sale price; anything else should show its list price. Anything with neither should read "Ask staff":

SELECT
    title,
    COALESCE(sale_price, list_price, 0) AS display_price
FROM records;

Output:
| title | display_price |
|---|---|
| Blue Train | 45.00 |
| Songs in the Key of Life | 48.00 |
| Unknown Pleasures | 30.00 |
| Blue | 22.00 |
| Kind of Blue | 55.00 |

For Coltrane's Blue Train, sale_price is NULL, so COALESCE moves to the next argument and prints the list price instead. For Stevie Wonder's record, the sale price exists, so that's what shows. This is the same logic as checking a stack of fallback options in order and stopping at the first one that's actually filled in. There's nothing more mysterious than that.

ISNULL and IFNULL: the single-fallback shortcut

Where COALESCE accepts any number of arguments, ISNULL (SQL Server) and IFNULL (MySQL, SQLite) take exactly two: a value and what to use if that value is NULL. They read a little more plainly for the simple case:

-- SQL Server
SELECT title, ISNULL(pressing_year, 0) AS year_known
FROM records;

-- MySQL / SQLite
SELECT title, IFNULL(pressing_year, 0) AS year_known
FROM records;

Output:
| title | year_known |
|---|---|
| Blue Train | 1957 |
| Songs in the Key of Life | 0 |
| Unknown Pleasures | 1979 |
| Blue | 1971 |
| Kind of Blue | 1959 |

Stevie Wonder's pressing year is unknown, so the placeholder 0 shows up instead of a blank cell. That's fine for a quick script. It's a poor choice for a customer-facing report, since a shopper reading "0" might think the record was pressed in the year zero rather than "we don't have that on file." A text placeholder communicates the gap far better:

SELECT title, IFNULL(condition_grade, 'Not yet graded') AS grade
FROM records;

Output:
| title | grade |
|---|---|
| Blue Train | VG+ |
| Songs in the Key of Life | NM |
| Unknown Pleasures | Not yet graded |
| Blue | VG |
| Kind of Blue | NM |

Because ISNULL/IFNULL only run on two engines a piece and only take two arguments, COALESCE stays the safer default for anything that needs to run on more than one database or needs more than one fallback.

NULLIF: turning a value into NULL on purpose

NULLIF runs the opposite direction. It compares two values, and if they match, it returns NULL instead of the value. This sounds backwards until you hit the one problem it exists to solve: division by zero.

The shop wants to know how many units of each record are still worth restocking, based on the ratio of stock sold to stock on hand. A record with zero units in stock would divide by zero and crash the report:

SELECT
    title,
    units_in_stock,
    list_price / NULLIF(units_in_stock, 0) AS price_per_unit
FROM records;

Output:
| title | units_in_stock | price_per_unit |
|---|---|---|
| Blue Train | 3 | 15.00 |
| Songs in the Key of Life | 1 | 60.00 |
| Unknown Pleasures | 0 | NULL |
| Blue | 2 | NULL |
| Kind of Blue | 4 | 13.75 |

For Unknown Pleasures, units_in_stock is 0, so NULLIF swaps it for NULL before the division runs. Dividing by NULL returns NULL rather than throwing an error, so the query finishes and simply reports "not applicable" for that row. Joni Mitchell's Blue returns NULL for a different reason: its list_price is missing, so the division has nothing to work with regardless of stock count. Two different gaps, same NULL result — which is exactly why it helps to combine tools rather than lean on just one.

Putting it together

Real reports rarely need just one of these functions. Here's the shop's actual end-of-week summary query, which prices each record for the shelf tag and flags anything still missing key details:

SELECT
    title,
    artist,
    COALESCE(CAST(pressing_year AS VARCHAR), 'year unknown') AS pressing_year,
    COALESCE(condition_grade, 'ungraded') AS condition_grade,
    COALESCE(sale_price, list_price, 0) AS shelf_price,
    CASE
        WHEN pressing_year IS NULL OR condition_grade IS NULL OR list_price IS NULL
        THEN 'Needs cataloguing'
        ELSE 'Ready'
    END AS status
FROM records;

Output:
| title | artist | pressing_year | condition_grade | shelf_price | status |
|---|---|---|---|---|---|
| Blue Train | John Coltrane | 1957 | VG+ | 45.00 | Ready |
| Songs in the Key of Life | Stevie Wonder | year unknown | NM | 48.00 | Needs cataloguing |
| Unknown Pleasures | Joy Division | 1979 | ungraded | 30.00 | Needs cataloguing |
| Blue | Joni Mitchell | 1971 | VG | 22.00 | Needs cataloguing |
| Kind of Blue | Miles Davis | 1959 | NM | 55.00 | Ready |

One query, four columns cleaned up, and a status flag built from IS NULL checks that tells the shop owner exactly which records still need attention before they go on the shelf. Nothing here is exotic. It's the same handful of functions, applied with a clear idea of what each blank cell is supposed to mean once it reaches a human reader.

That's really the whole discipline: NULL isn't a data quality failure to panic over, and it isn't something to paper over with a random default either. It's a fact about the world — a record whose year nobody wrote down, a price nobody set yet — and your query should say so, on purpose, in whatever way fits the report you're building.