SQL Help

NULL vs empty string: they are not the same

Published August 2026 · completing the classic SQL Help series

The distinction fits in one line:

A customer whose middle name you never asked for: NULL. A customer who told you they have no middle name: ''. They print identically on a web page, and behave completely differently in every query — which is why mixing them corrupts your logic quietly.

NULL infects comparisons

Any ordinary comparison with NULL yields neither true nor false but unknown — and WHERE only keeps rows that are definitely true:

SELECT * FROM customers WHERE middle_name =  '';     -- finds the known-empty ones
SELECT * FROM customers WHERE middle_name =  NULL;   -- finds NOTHING, ever
SELECT * FROM customers WHERE middle_name IS NULL;   -- the correct spelling
SELECT * FROM customers WHERE middle_name IS NOT NULL;

The sneaky version is the negative filter. "Everyone not named Smith":

SELECT * FROM customers WHERE last_name <> 'Smith';

Rows where last_name is NULL are excluded — the comparison is unknown, not true. If unknowns should count as "not Smith", say so: WHERE last_name <> 'Smith' OR last_name IS NULL, or use the NULL-safe operator NOT (last_name <=> 'Smith').

Aggregates skip NULLs — use it deliberately

SELECT COUNT(*)            AS all_rows,      -- counts rows
       COUNT(middle_name)  AS with_value,    -- counts non-NULL values
       COUNT(*) - COUNT(middle_name) AS missing
FROM customers;

This is a feature: it's how you count missing data in one pass. It's also the trap from the joins article — if you store '' where you mean "unknown", COUNT(middle_name) counts it as present and your "profile completeness" numbers lie. AVG, SUM, MIN, MAX all skip NULLs the same way; an AVG over a column padded with fake zeros instead of NULLs is just wrong.

Where NULLs come from whether you like it or not

Even a database with no stored NULLs generates them: every unmatched row in a LEFT JOIN arrives as NULLs. That's why the display-time translation belongs in the query, not in the storage:

SELECT c.name,
       COALESCE(p.phone, 'no phone on file') AS phone
FROM customers c
LEFT JOIN phones p ON p.customer_id = c.customer_id;

COALESCE(a, b, ...) returns the first non-NULL argument — store the honest NULL, print the friendly label. Its cousin NULLIF(a, b) goes the other way, and is the standard repair tool for imported data where empty strings crept in (fix it during import when you can):

UPDATE customers SET middle_name = NULLIF(TRIM(middle_name), '');

So which should your schema use?

• Can the value genuinely be unknown or not-applicable? Allow NULL and store NULL for those cases. Don't invent sentinel values — '', 0, '1900-01-01' and 'N/A' are all lies the database can't warn you about later.
• Is the value required? Declare NOT NULL and let the database enforce it at insert time. A surprising number of columns fall in this bucket — a NOT NULL default is a fine schema habit, relaxed only where "unknown" is real.
• Unique columns: note that MySQL allows multiple NULLs in a UNIQUE index (unknowns aren't equal to each other). Often that's exactly right — optional-but-unique-when-present, like an email column.

One rule to leave with: the database stores what you know — including knowing nothing. NULL is how you say it honestly, and every function above exists to make honesty convenient.