NULL Values: Beginner

Tell the difference between empty and missing

NULL is not a value; it is the absence of a value. Think of it as a placeholder that says "we don't know what goes here" or "this information wasn't provided." This is fundamentally different from zero, an empty string, or the word "null" as text.

Imagine a survey asking for income. NULL means the person skipped the question entirely. Zero means they answered and said they have no income. An empty string would typically be an input error.

Real-world data is incomplete. Not every customer provides a phone number. Not every product has a discount price. Not every employee has a manager. NULL lets databases store this reality without forcing fake values into columns.

Sarah Chen has NULL for manager_id because she is the CEO and has no manager. This is accurate information, not missing data.

SELECT
  order_id,
  region
FROM orders
WHERE region ___ ___

Understanding the distinction between NULL, zero, and empty string is foundational because each requires a different query pattern and represents a fundamentally different real-world situation.

NULL as a concept reflects an honest acknowledgment that some information is simply unavailable, and databases that store NULL correctly are more truthful than those that substitute zeros or placeholder text.

When you encounter unexpected results in a query involving optional columns, checking whether those columns contain NULL values is almost always the first diagnostic step to take.

Predict how NULLs change query results

Most programming uses two-valued logic: things are either true or false. SQL uses three-valued logic: true, false, or unknown. Any comparison involving NULL returns unknown, not true or false.

In WHERE clauses, rows only pass through when the condition is true. Unknown conditions filter out the row just like false does.

Users with NULL ages are excluded because comparing NULL to any value yields unknown. They don't appear in the results even though they might be 25 years old.

Which query correctly finds rows where region is NULL?

Now practice with an aggregate function to see how NULL values are excluded from calculations.

SELECT
  ___(___) AS avg_discount
FROM orders

Three-valued logic is not just an academic curiosity; it has practical consequences every time you write a WHERE clause that filters on a column that can be NULL, because UNKNOWN rows are silently excluded from the result.

The reason NULL does not equal itself is logically sound: if the value is unknown, you cannot make any truthful claim about whether two unknown values are the same or different.

Building the habit of asking "can this column be NULL?" before writing comparison conditions prevents an entire class of silent filtering errors that produce results that look correct but exclude important rows.

Find or exclude missing values

Since you can't use = or != to check for NULL, SQL provides special operators: IS NULL and IS NOT NULL. These are the only correct ways to filter for NULL values.

Use IS NULL to find rows where a column has no value:

This returns only customers who have not provided a phone number. Using WHERE phone = NULL would return zero rows because that comparison is always UNKNOWN.

Use IS NOT NULL to find rows where a column has any value:

This returns customers who have provided phone numbers, excluding anyone with NULL.

The first query returns nothing because comparing NULL to itself doesn't yield true. The second query correctly identifies NULL values using IS NULL.

SELECT
  name,
  phone
FROM customers
WHERE phone ___ ___ ___

IS NULL and IS NOT NULL are the only correct tools for testing whether a value is absent, and memorizing this as a rule rather than a guideline will save you from a common category of hard-to-debug errors.

IS NOT NULL is especially useful at the start of a WHERE clause to exclude incomplete records before applying further conditions, ensuring those conditions are evaluated against known values rather than unknowns.

The combination of IS NULL for finding missing data and IS NOT NULL for excluding it covers the vast majority of NULL-filtering scenarios you will encounter in practice.

Count accurately when data is missing

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX handle NULL in specific ways that can produce unexpected results if you don't understand the rules.

Consider this orders table with some NULL values in the discount column:

COUNT has two distinct behaviors depending on what you count:

SELECT
  ___(___) AS non_null_count
FROM orders

The difference between COUNT(*) and COUNT(column) is one of the most practical distinctions in SQL, because using the wrong one can make a dataset appear more complete than it actually is.

Using both COUNT(*) and COUNT(column) together in a single query is a quick and readable data quality check that immediately shows how many rows have a value in the column of interest.

When reporting on data completeness to stakeholders, the gap between COUNT(*) and COUNT(column) is the number you want to highlight because it represents records that may need to be followed up on.

Get correct totals despite missing values

Beyond COUNT, other aggregate functions also interact with NULL values in ways that can quietly affect your results.

SUM and AVG skip NULL values entirely. They don't treat NULL as zero; they pretend those rows don't exist for the calculation.

The SUM is 10 + 15 + 25 = 50 (NULL values ignored). The AVG is 50 / 3 = 16.67 (dividing by 3 non-NULL values, not 5 total rows).

MIN and MAX also ignore NULL values when finding extremes in a column.

MIN and MAX only consider non-NULL values. This is usually the desired behavior, but be aware that it means "minimum discount applied" not "minimum discount overall."

What happens when ALL values in a column are NULL?

When all values are NULL, SUM and AVG return NULL (not zero), while COUNT returns 0. This distinction can cause issues if you later do calculations with these results.

Use NULL checks to audit data completeness:

Use the IS NULL operator to identify rows where a column has no value assigned.

SELECT
  ___ - ___ AS missing_discounts
FROM orders

The fact that SUM and AVG silently skip NULLs rather than treating them as zero means you must always be clear about whether your business definition of "average discount" includes or excludes orders that had no discount.

When aggregate functions return NULL because all input values are NULL, you can protect downstream calculations by wrapping the aggregate with COALESCE to substitute a sensible default.

Data audits using COUNT(*) minus COUNT(column) are one of the fastest ways to quantify data quality issues in a table and communicate the scope of missing data to the team responsible for fixing it.