beginner~3h

SQL Fundamentals Deep Dive — Data Types, NULL Handling & Query Execution Order

The data type you pick, how you handle NULL, and the order SQL actually evaluates a query's clauses in are three foundational things that quietly cause a disproportionate share of real-world SQL bugs.

Learning objectives

Explain why NULL = NULL evaluates to unknown rather than true, and what that means for WHERE clauses.
Choose the correct data type for money, text, and timestamps, and explain why the wrong choice causes real bugs, not just style complaints.
State SQL's logical clause execution order and use it to predict which aliases are visible in which clauses.

Three quiet, foundational misunderstandings — the wrong data type, mishandled NULLs, and not knowing SQL's real clause evaluation order — account for a disproportionate share of real production SQL bugs, precisely because the query still runs and returns an answer; it's just the wrong one.

A query built on a wrong assumption about NULL, or on a data type that can't represent the value exactly (floating point for money), doesn't fail loudly — it silently returns a plausible-looking wrong answer, which is far more dangerous than a query that errors outright.

NULL — represents "unknown," not zero or empty; NULL = NULL evaluates to unknown, not true. NUMERIC — exact, arbitrary-precision decimal storage, correct for money. TIMESTAMPTZ — a timestamp stored as an unambiguous instant, converted to the session's time zone on read. Logical execution order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, which determines which clause can reference which alias.

📖 Story

A new engineer writes WHERE phone_number = NULL expecting it to find every customer with no phone on file, ships it, and it returns zero rows — every single time — no matter how many customers genuinely have no phone number. The query isn't broken syntactically; it's built on a wrong mental model of what NULL actually means in SQL, and that same wrong mental model — plus not knowing which data type actually fits which values, plus not knowing which part of a query the database evaluates first — accounts for a disproportionate share of real-world SQL bugs.

Choosing the right data type is a correctness decision, not a style choice

Numeric: INTEGER/BIGINT for whole numbers; NUMERIC(p,s) for exact decimal values (money — never use floating point for currency, since binary floats can't represent most decimal fractions exactly); REAL/DOUBLE PRECISION only for genuinely approximate scientific values.
Text: VARCHAR(n) when a real maximum length is meaningful; TEXT when it isn't — in PostgreSQL both are stored identically, so VARCHAR's only real effect is enforcing a length constraint.
Date/time: TIMESTAMPTZ (timestamp with time zone) for almost everything — it stores an unambiguous instant and converts to the querying session's time zone on read; a plain TIMESTAMP stores wall-clock time with no time zone context at all, a frequent source of bugs across regions.
JSONB: for genuinely semi-structured data, stored in a parsed binary form that supports indexing — not a replacement for real columns just because it's flexible.

NULL means "unknown," not "empty" or "zero"

NULL represents the absence of a known value — it is not zero, not an empty string, and critically, it is not even equal to itself. NULL = NULL evaluates to NULL (unknown), not TRUE — which is exactly why WHERE x = NULL never matches anything; the correct form is WHERE x IS NULL. Any arithmetic or comparison involving NULL propagates to NULL (unknown) rather than TRUE/FALSE.

The order SQL actually evaluates a query — not the order you write it in

Even though you write SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, the database evaluates it roughly as: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This is exactly why a column alias defined in SELECT can't be referenced in that same query's WHERE clause (WHERE runs before SELECT exists), but can be referenced in ORDER BY (which runs after).

Why NUMERIC is exact but REAL/DOUBLE isn't

NUMERIC(p,s) stores digits directly in a base-10, arbitrary-precision representation — it can represent 0.10 exactly, because it isn't trying to encode it in binary. REAL/DOUBLE PRECISION store values in IEEE 754 binary floating point, which cannot represent most decimal fractions exactly (0.10 is actually stored as a very close binary approximation) — for money, that tiny, silent imprecision compounds across enough transactions to become a real, visible discrepancy.

How three-valued logic actually propagates

SQL's WHERE clause doesn't just evaluate to TRUE/FALSE — it evaluates to TRUE/FALSE/UNKNOWN (NULL's logical value), and only rows where the final result is exactly TRUE are kept; both FALSE and UNKNOWN rows are excluded. This is why NOT (x = NULL) still excludes the row — x = NULL is UNKNOWN, and NOT UNKNOWN is still UNKNOWN, not TRUE.

Why the logical execution order matters for the planner too

The planner doesn't literally execute steps in that logical order — it can push a WHERE filter down before a join, or reorder joins entirely — but the logical result must always be equivalent to evaluating in that order. Understanding the logical order is what lets you predict, correctly, which clauses can reference which aliases, and why an aggregate filter belongs in HAVING rather than WHERE (since WHERE runs before GROUP BY produces any groups to filter).

Pick a data type based on what the value actually represents, not what's convenient to type — money is always NUMERIC, never REAL/FLOAT; timestamps are almost always TIMESTAMPTZ, never a bare TIMESTAMP, unless you have a specific, deliberate reason.
Any time you need to check for a missing value, use IS NULL/IS NOT NULL — never = NULL/!= NULL, which silently return no rows rather than erroring.
Use COALESCE(column, default_value) to substitute a concrete value for NULL wherever downstream logic (display, arithmetic, comparisons) needs a non-null value to work correctly.
When writing a query with both a WHERE and a HAVING, put row-level filters (before grouping) in WHERE, and put aggregate-level filters (after grouping, like HAVING COUNT(*) > 5) in HAVING — never the reverse.
When a query seems to reference an alias in the wrong place and fails, mentally re-run the logical execution order (FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY) to see exactly why that alias isn't visible there yet.

Choose NUMERIC for any monetary or exact-decimal value, without exception — never REAL/DOUBLE PRECISION, regardless of how small the values seem.
Default to TIMESTAMPTZ for new timestamp columns unless you have a specific, documented reason to store naive wall-clock time instead.
Always test NULL-handling logic with actual NULL values in your test data — a test suite built entirely from non-null sample rows will never catch a = NULL bug.
Use COALESCE explicitly at the boundary where a NULL would otherwise cause a problem (display, arithmetic, a NOT NULL insert), rather than hoping NULLs never occur.
Internalize the logical execution order well enough to predict, without testing, whether an alias will be visible in a given clause — it removes an entire category of "why won't this query even parse" confusion.

⚠️ Why this keeps happening

NULL and floating-point behave in ways that quietly contradict how most other programming languages handle "no value" and "decimal numbers" — so the intuition engineers bring from application code is often exactly wrong here, and the query still runs (it just returns the wrong answer) rather than failing loudly.

Writing WHERE column = NULL expecting it to match missing values — it always returns zero rows, silently, with no error to flag the mistake.
Storing money as FLOAT/REAL. Individually tiny rounding errors compound across enough rows/transactions into a real, visible discrepancy that's painful to trace back to its root cause.
Storing timestamps as naive TIMESTAMP instead of TIMESTAMPTZ across a system that later spans multiple time zones — every stored value becomes ambiguous about which zone it was written in.
Putting an aggregate condition in WHERE instead of HAVING (e.g., WHERE COUNT(*) > 5) — this fails outright, because WHERE runs before GROUP BY has produced any groups or aggregates to filter on.
Assuming NOT IN (subquery) behaves like NOT EXISTS when the subquery can return a NULL. If the subquery's result set contains even one NULL, NOT IN silently returns zero rows for the entire outer query — a famously sharp NULL-related trap.

Choosing the smallest correct integer type (INTEGER vs BIGINT) matters for storage and index size at scale — but never shrink a type purely for performance if it risks overflow; correctness always outranks the marginal space savings.
VARCHAR(n) versus TEXT has no performance difference in PostgreSQL specifically — choose based on whether a length constraint is semantically meaningful, not for any speed reason.
Filtering with IS NULL/IS NOT NULL can use a partial index (CREATE INDEX ... WHERE column IS NOT NULL) when only a subset of rows have a non-null value in a sparsely populated column.
Understanding logical execution order lets you push filters into WHERE (evaluated early, before grouping) rather than HAVING (evaluated late) whenever the filter is genuinely row-level — this can meaningfully reduce the number of rows the database has to group and aggregate.
COALESCE inside a WHERE clause on an indexed column can prevent the planner from using that column's index at all — prefer IS NULL/IS NOT NULL checks directly over wrapping the column in a function when an index matters.

A WHERE column = NULL bug (silently matching zero rows) in an authorization check — e.g., checking whether a revoked_at column is unset — can fail in either direction depending on the exact logic, so any NULL-sensitive condition in security-relevant code deserves explicit IS NULL/IS NOT NULL review, not just a functional test.

Add automated tests that explicitly insert NULL values into nullable columns and assert expected query behavior — this exact class of bug reliably escapes test suites built only from non-null sample data, and won't show up in any monitoring dashboard until a report is visibly wrong.

Add NOT NULL constraints and explicit defaults wherever a column should genuinely never be missing — don't rely on application code alone to guarantee it; the database is the last line of defense.
Standardize on TIMESTAMPTZ for all new timestamp columns across the team as a written convention, specifically to prevent the "which time zone was this stored in" class of bug before it starts.
Review any monetary column's type during schema review specifically — a FLOAT/REAL used for money is exactly the kind of mistake that's cheap to catch in review and expensive to migrate away from later.
Add automated tests that explicitly insert NULL values into nullable columns and assert the expected query behavior — don't rely on production data eventually surfacing the bug.
Document your team's logical-execution-order intuition as onboarding material for new engineers — this one mental model prevents a disproportionate number of "why doesn't this query work" questions.

Insert a row with a NULL phone number, then run both WHERE phone_number = NULL and WHERE phone_number IS NULL — confirm only the second one finds it.
Store the value 0.10 in both a NUMERIC(10,2) column and a REAL column, then sum a thousand copies of each and compare — observe the floating-point drift.
Write a query with GROUP BY and confirm that referencing a SELECT-defined alias works in ORDER BY but fails in WHERE.
Build a NOT IN subquery where the subquery's result can include a NULL, confirm the outer query silently returns zero rows, then rewrite it using NOT EXISTS and confirm it now returns the correct rows.

✓ Quick recap

NULL means "unknown" — it isn't equal to anything, not even another NULL; always use IS NULL/IS NOT NULL, never = NULL.
Use NUMERIC for money and any exact-decimal value; REAL/DOUBLE PRECISION is for genuinely approximate values only.
Prefer TIMESTAMPTZ over a naive TIMESTAMP for almost every timestamp column.
SQL's logical execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — this determines which aliases are visible where.
NOT IN silently breaks if its subquery can return a NULL; NOT EXISTS doesn't have this trap.

Want a visual for this concept?

Generate a diagram tailored to “SQL Fundamentals Deep Dive — Data Types, NULL Handling & Query Execution Order” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Practice interview questions on this topic →← Back to all SQL chapters