NULL Values: Intermediate

Spot disguised missing data in real tables

In an ideal world, NULL would always represent missing or unknown data. In reality, you'll encounter "fake NULLs": empty strings, zeros, and special sentinel values that systems use to mean "unknown" because they couldn't store a real NULL.

Legacy systems often use placeholder values instead of proper NULLs to represent missing data.

CRM System

Form requires a value, so 0 is entered for unknown company size, skewing averages.

E-commerce Platform

Import script sets empty string instead of NULL, causing blank product descriptions.

HR Database

Active employees get termination_date 9999-12-31, breaking tenure calculations.

Empty string ('') and NULL are fundamentally different, but many systems conflate them:

The difference can be dramatic. In this example, checking only IS NULL captures 1,247 missing phone numbers, but the real count of "unknown" phones is 8,934 when including empty strings.

The best approach is to convert sentinel values to proper NULLs at query time using NULLIF (covered later in this lesson). This normalizes your data before analysis:

This pattern converts sentinel values to NULL so that aggregate functions, JOINs, and filters all behave correctly. The rest of this lesson teaches you the tools to do this effectively.

Practice using NULLIF to convert empty strings into proper NULL values.

SELECT
  user_id,
  ___(___, ___) AS clean_email
FROM users

Sentinel values are a form of technical debt that accumulates when systems are designed without proper NULL support, and cleaning them at query time using NULLIF is the standard workaround until the data can be fixed at the source.

The habit of running SELECT DISTINCT on every optional column before writing analytical queries is one of the fastest ways to surface hidden sentinel values that would otherwise corrupt aggregations.

Keep rows safe when joins hit missing keys

NULL values create specific behaviors in JOIN operations that can lead to unexpected results if you don't understand the rules. The key insight: NULL never equals anything, not even another NULL, so NULLs never match in JOIN conditions.

Consider these two tables for our JOIN examples:

With INNER JOIN, rows with NULL join keys never match anything, so they're excluded from results on both sides:

Notice: ORD-3 (with NULL customer_id) is missing. The "Guest" customer (with NULL customer_id) is also missing. C-103 and C-104 don't appear because they have no matching orders.

See how the join evaluates each key pair, including the rows where one or both sides contain NULL.

LEFT JOIN preserves all rows from the left table, but NULL join keys still don't match:

ORD-3 now appears, but with NULL for name because its NULL customer_id didn't match the NULL customer_id in customers. ORD-4 appears with NULL name because C-103 doesn't exist in customers.

Observe how the LEFT JOIN preserves all left-side rows, filling unmatched right-side columns with NULL.

Even though E-103 has a NULL dept_id and D-99 does not exist, every employee row survives in the output. This preservation is what makes LEFT JOIN essential for detecting incomplete data.

If you need NULLs to match NULLs, you must handle it explicitly:

Now ORD-3 matches the "Guest" customer because we explicitly check for both sides being NULL. This pattern is useful when NULL represents a meaningful category like "guest" or "unknown."

How does the JOIN type affect rows with NULL keys?

Try writing a LEFT JOIN that also matches rows where both join keys are NULL.

SELECT
  *
FROM orders AS o
LEFT JOIN customers AS c
  ON o.cust_id = c.cust_id
  ___ (
  o.cust_id ___
  ___ c.cust_id ___
)

The rule that NULL join keys never match is one of the most consequential behaviors in SQL because it causes silent row loss without any error or warning, making it one of the hardest classes of bug to detect in production.

When you discover that an INNER JOIN is returning fewer rows than expected, checking whether join key columns contain NULLs is the first diagnostic step and often the cause of the discrepancy.

The explicit OR (a IS NULL AND b IS NULL) pattern for matching NULL keys is a deliberate design choice, not a workaround, because it forces you to consciously decide whether two absent values represent the same entity in your domain.

Replace missing values with useful defaults

COALESCE returns the first non-NULL value from a list of arguments. It's essential for providing default values when data might be missing and for making query results more readable.

COALESCE returns the first non-NULL value from a list of arguments, providing a clean way to handle missing data.

COALESCE evaluates arguments left to right and returns the first non-NULL value it finds.

For each customer, this returns their nickname if they have one, otherwise their first_name, otherwise 'Unknown'. It cascades through options until finding a non-NULL value.

Watch how COALESCE walks through columns left to right, returning the first non-NULL value it finds for each row.

When both phone and email are NULL (C-3), COALESCE falls through to the fallback string "Unknown". This cascading logic ensures every row gets a usable value.

Without COALESCE, the calculation price - discount would return NULL whenever discount is NULL. COALESCE(discount, 0) treats missing discounts as zero, ensuring the math works.

Remember that SUM of all NULLs returns NULL, not zero. COALESCE can fix this:

If a category has no inventory records (or all NULL quantities), the result shows 0 instead of NULL, making downstream calculations and reports cleaner.

COALESCE is particularly useful for handling NULL values that appear after LEFT JOINs:

Orders without matching customers now show "Guest Order" instead of NULL, making reports more readable.

See how LEFT JOIN and COALESCE work together: unmatched orders receive "Guest Order" instead of a raw NULL in the display name.

For matched customers, COALESCE picks the nickname when available and falls back to the full name. For unmatched or NULL-keyed orders, the final fallback "Guest Order" ensures every row has a readable label.

SELECT
  product_id,
  price - ___(___, ___) AS final_price
FROM products

COALESCE is the single most frequently used NULL-handling function in production SQL because providing a sensible default is almost always preferable to propagating NULL into reports and dashboards.

The cascading nature of COALESCE makes it ideal for priority hierarchies like "use the customer-provided name, then the system name, then a generic label" without needing nested CASE expressions.

Wrapping aggregate functions with COALESCE to substitute zero or an empty string is a standard pattern for making reports resilient to groups that have no data, preventing NULL from appearing in summary tables.

Convert fake values back to proper NULLs

NULLIF does the opposite of COALESCE: it returns NULL when two values are equal. This is useful for converting sentinel values (like 0, -1, or empty strings) into proper NULLs.

NULLIF returns NULL if two values are equal, otherwise it returns the first value. It is the inverse of COALESCE.

NULLIF compares two expressions and returns NULL when they match.

Legacy systems often use special values like 0, -1, or empty strings to mean "unknown." NULLIF converts these to proper NULLs:

The legacy system used 0 to mean "age unknown." NULLIF(age, 0) converts these zeros to NULL so they're handled correctly by aggregate functions.

A powerful pattern: use NULLIF to prevent division-by-zero errors:

NULLIF(total_orders, 0) returns NULL when total_orders is zero. Dividing by NULL returns NULL (instead of an error), gracefully handling products with no orders.

Empty strings often represent missing data. NULLIF converts them to proper NULLs.

This converts empty strings to NULL, ensuring consistent handling of missing contact info.

SELECT
  product_id,
  total_amount / ___(___, ___) AS avg_value
FROM transactions

The NULLIF division-by-zero pattern is one of those techniques that becomes second nature once you learn it because it elegantly converts a potential runtime error into a predictable NULL result that downstream code can handle.

NULLIF is most powerful when used at the beginning of a data pipeline to normalize sentinel values into proper NULLs, because every downstream query benefits from not having to repeat the sentinel-detection logic.

Combining NULLIF to clean sentinel values with COALESCE to supply defaults is the complete pattern for robust NULL handling, covering both the case where a value is absent and the case where it has been stored as a placeholder.

Chain NULL handlers for clean output

COALESCE and NULLIF work together powerfully. A common pattern is to clean data with NULLIF, then provide defaults with COALESCE:

Nesting NULLIF inside COALESCE creates a clean and default pattern that handles both empty strings and NULLs.

This first converts empty strings to NULL (via NULLIF), then cascades through options (via COALESCE) to find a usable name.

SELECT
  product_id,
  ___(
    ___(display_name, ''),
    'Unknown'
    ) AS name
FROM products

The COALESCE + NULLIF pattern is the standard way to handle both NULL and empty-string sentinel values in a single expression.

NULLIF is also useful for preventing division by zero: dividing by NULLIF(denominator, 0) returns NULL instead of crashing.

COALESCE can accept any number of arguments and returns the first non-NULL one, making it flexible for multi-level fallback chains.