NULL vs Empty String in SQL: They Are Not the Same

An empty string is a value — a string of length zero. NULL is the absence of any value at all. Every SQL database except Oracle treats them as completely different things, and confusing them produces some of the most maddening bugs in data work: filters that silently miss rows, counts that don't add up, and "identical" tables that refuse to reconcile.

Why doesn't WHERE col = '' find my NULL rows?

Because of three-valued logic. Any comparison involving NULL — even NULL = NULL — evaluates to UNKNOWN, not TRUE, and WHERE only keeps rows where the condition is TRUE. So col = '' is UNKNOWN for NULL rows and they're filtered out. The flip side bites harder: col <> '' also excludes NULL rows, which is how "not empty" filters silently drop missing data.

-- phone values: 'x555-0100', '', NULL

SELECT COUNT(*) FROM contacts WHERE phone = '';        -- 1 (empty only)
SELECT COUNT(*) FROM contacts WHERE phone IS NULL;     -- 1 (NULL only)
SELECT COUNT(*) FROM contacts WHERE phone <> '';       -- 1 (NULL excluded!)

-- Catch both "missing" flavors:
SELECT COUNT(*) FROM contacts
WHERE phone IS NULL OR phone = '';                     -- 2

-- Or more compactly:
SELECT COUNT(*) FROM contacts WHERE COALESCE(phone, '') = '';

NULL always needs the dedicated predicates IS NULL / IS NOT NULL. If three-valued logic is new territory, start with our broader guide to NULL handling in SQL.

How do COUNT and other aggregates treat each?

Aggregates skip NULLs but count empty strings like any other value. COUNT(col) counts non-NULL values — so empty strings are included — while COUNT(*) counts rows regardless. The same rule drives AVG, SUM, MIN, and MAX, which is why a column full of empty strings behaves very differently from one full of NULLs.

-- phone values: 'x555-0100', '', NULL

SELECT
    COUNT(*)      AS total_rows,      -- 3
    COUNT(phone)  AS non_null,        -- 2  ('' counts, NULL doesn't)
    COUNT(*) - COUNT(phone) AS nulls  -- 1
FROM contacts;

-- GROUP BY keeps them separate too:
SELECT phone, COUNT(*) FROM contacts GROUP BY phone;
-- ''    -> 1
-- NULL  -> 1   (grouped together with other NULLs, but apart from '')

This split regularly breaks "percent complete" metrics: a data-entry system writing '' instead of NULL makes a column look 100% populated while carrying no information.

What is Oracle's empty-string quirk?

Oracle stores a zero-length VARCHAR2 as NULL — inserting '' and inserting NULL are the same operation. This predates the SQL standard and Oracle keeps it for backward compatibility. Consequences: WHERE col = '' can never match anything in Oracle (it's effectively col = NULL, always UNKNOWN), and code ported between Oracle and anything else must be re-checked around every empty-string comparison.

-- Oracle only:
INSERT INTO t (name) VALUES ('');      -- stores NULL!
SELECT COUNT(*) FROM t WHERE name = '';        -- always 0
SELECT COUNT(*) FROM t WHERE name IS NULL;     -- finds the row

-- PostgreSQL / MySQL / SQL Server:
INSERT INTO t (name) VALUES ('');      -- stores a real empty string
SELECT COUNT(*) FROM t WHERE name = '';        -- finds the row

If a system must run on both Oracle and another engine, the only safe stance is to treat '' and NULL as interchangeable at the application boundary — normalize on write, and use NULL-safe predicates on read.

How do I clean up mixed NULL and empty-string data?

Two functions do all the work. COALESCE(col, fallback) replaces NULL with a value — useful for display and for making comparisons total. NULLIF(col, '') does the reverse: it turns empty strings into NULL, collapsing both "missing" flavors into one canonical form. Chained together they normalize any messy text column.

-- Normalize for analysis: everything missing becomes NULL
SELECT NULLIF(TRIM(phone), '') AS phone_clean
FROM contacts;
-- TRIM first so '  ' (whitespace-only) also collapses

-- Normalize for display: everything missing becomes a label
SELECT COALESCE(NULLIF(TRIM(phone), ''), 'unknown') AS phone_display
FROM contacts;

-- Permanent fix, then keep it fixed:
UPDATE contacts SET phone = NULL WHERE TRIM(phone) = '';
ALTER TABLE contacts
    ADD CONSTRAINT phone_not_empty CHECK (phone <> '');

That CHECK constraint is the underrated part: after cleanup it guarantees the column carries exactly one representation of "missing" forever. Both functions are covered in depth in COALESCE and NULLIF.

Which should my schema use for missing text?

Prefer NULL for "we don't know" and reserve '' for the rare case where "known to be empty" is genuinely meaningful (say, a user deliberately cleared their bio). What matters far more than the choice is consistency: pick one convention per column, enforce it with a constraint or normalize in the load, and joins, GROUP BYs, and completeness metrics all become trustworthy again. Mixed conventions are the real enemy — two "identical" customer tables that disagree only in ''-vs-NULL will never reconcile with a plain equality join, because the NULL side matches nothing.