Handling NULL Values in SQL

Good to see you again. In the last lesson, you learned how WHERE filters rows using comparisons, Boolean logic, IN, BETWEEN, and LIKE. One important detail was deliberately left open: a condition involving a missing value does not behave like an ordinary true-or-false comparison.

This lesson closes that gap. You will learn to identify missing data with IS NULL and IS NOT NULL, display or calculate with sensible fallbacks using COALESCE, and write filters that state clearly how records with missing values should be treated. These habits matter directly in reporting QA: a metric can be wrong simply because rows with an unknown status, region, or amount were silently excluded.

---

NULL means missing or unknown—not zero or an empty string

In SQL, NULL represents an absent, unknown, or not-applicable value. It is not the number 0, not an empty string '', and not text such as 'Unknown'.

Consider this simplified orders data:

order_id	region	discount_amount	sales_rep
101	East	15	Amina
102	West	NULL	Daniel
103	NULL	0	Priya
104	Central	NULL	NULL

Each missing value can mean something different:

• discount_amount = 0 means the order is known to have no discount.
• discount_amount = NULL means the discount is unknown or not recorded.
• region = NULL may indicate an incomplete customer profile or a failed data mapping.
• sales_rep = NULL might be valid for self-service orders, but a defect for enterprise sales.

The database cannot decide the business meaning for you. Before replacing, excluding, or counting NULL values, define what they mean in that particular field.

In Snowflake, an empty string is a real text value with zero characters:

SELECT
    '' AS empty_string,
    NULL AS missing_value;

They may look similar in a results grid, but they carry different meaning. For QA work, inspect them separately if both are possible in the source:

SELECT
    order_id,
    sales_rep
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE sales_rep IS NULL
   OR sales_rep = '';

This checks for genuinely missing representatives and representatives stored as empty text. Whether empty strings should be treated as missing is a data-quality rule, not an automatic SQL assumption.

---

Why = NULL does not work

A common beginner query is:

-- Incorrect: returns no rows
WHERE discount_amount = NULL;

This is incorrect because NULL means “unknown.” SQL cannot determine whether an unknown amount equals another unknown amount. The result is unknown, not TRUE.

Similarly, these conditions do not identify missing values:

WHERE discount_amount <> NULL;

WHERE NOT (discount_amount = NULL);

SQL conditions have three possible results:

Logical result	Meaning in a WHERE clause
TRUE	The row is returned.
FALSE	The row is excluded.
UNKNOWN	The row is also excluded.

For example, assume discount_amount is NULL:

discount_amount > 0

SQL cannot say whether an unknown discount is greater than zero, so the result is UNKNOWN. Because WHERE keeps only TRUE, that row disappears from the result.

This has a major reporting consequence. Suppose you write:

WHERE order_status <> 'CANCELLED'

It may sound like “keep every order except cancelled ones.” But rows where order_status is NULL are not included, because:

NULL <> 'CANCELLED'

is UNKNOWN, not TRUE.

That might be correct if unknown statuses should be excluded from a completed-order report. It is wrong if the report is intended to show all non-cancelled orders, including records whose status has not yet been assigned. SQL will not infer your intention; write it explicitly.

---

Use IS NULL and IS NOT NULL to test missingness

IS NULL and IS NOT NULL are the correct SQL operators for testing whether a value is missing. Unlike = NULL, they return a definite Boolean result.

SELECT
    order_id,
    region,
    sales_rep
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE region IS NULL;

This returns only orders with no recorded region.

To return records that contain a value:

SELECT
    order_id,
    region,
    sales_rep
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE sales_rep IS NOT NULL;

Snowflake documents these operators as conditional expressions that return TRUE or FALSE.

Common QA patterns

Find incomplete records

SELECT
    order_id,
    customer_id,
    order_date
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE customer_id IS NULL;

If customer_id is mandatory, every returned row is a data-quality exception.

Require a complete record for a specific analysis

SELECT
    order_id,
    quantity,
    unit_price
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE quantity IS NOT NULL
  AND unit_price IS NOT NULL;

This is appropriate before calculating revenue if an unknown quantity or price makes the calculation unusable.

Find records missing either required field

SELECT
    order_id,
    billing_country,
    customer_email
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE billing_country IS NULL
   OR customer_email IS NULL;

Use OR here because one missing field is enough for the row to require review.

Find records missing both fields

WHERE billing_country IS NULL
  AND customer_email IS NULL;

This is a narrower test: it finds only the rows that have neither field populated.

---

Write filters that make the treatment of missing data explicit

The previous lesson showed that AND and OR require careful parentheses. With NULL, you must also decide whether unknown values belong in the result.

Example: retain unknown statuses for investigation

Suppose the requirement is:

Exclude cancelled orders, but retain orders with an unknown status so they can be reviewed.

Write:

SELECT
    order_id,
    order_status,
    order_date
FROM PORTFOLIO_DB.RAW.SALES_ORDER
WHERE order_status <> 'CANCELLED'
   OR order_status IS NULL;

The second condition is essential. Without it, unknown statuses would be silently removed.

Example: only include confirmed completed orders

Now suppose the requirement is stricter:

Include only orders explicitly marked completed.

Write:

WHERE order_status = 'COMPLETED'

Here, NULL statuses are rightly excluded because they are not confirmed as completed. You do not need to add AND order_status IS NOT NULL; equality with 'COMPLETED' already excludes missing values.

NOT IN has the same issue

This filter excludes NULL regions:

WHERE region NOT IN ('East', 'West')

That may be what you want. But if the business rule is “show every region except East and West, including unassigned regions,” write:

WHERE region NOT IN ('East', 'West')
   OR region IS NULL;

A useful review question is:

If this column is missing, should the row be included, excluded, or shown as an exception?

Answer that question before finalizing the filter.

Comparing two potentially missing columns

Normal equality also becomes unknown when either side is missing:

WHERE shipping_email = billing_email

If both emails are NULL, this does not evaluate to TRUE.

For Snowflake QA cases where two missing values should be considered equivalent, use null-safe comparison:

WHERE shipping_email IS NOT DISTINCT FROM billing_email;

This treats two NULL values as equal. Conversely, use this to find true mismatches, including “one populated, one missing” cases:

WHERE shipping_email IS DISTINCT FROM billing_email;

Use this deliberately. Two missing emails may be “equivalent” in a technical comparison, but they may still represent a business-data defect.

---

COALESCE: choose the first available value

COALESCE returns the first expression in its list that is not NULL.

COALESCE(value_1, value_2, value_3)

For each row, SQL checks the expressions in order and returns the first available value. If all inputs are NULL, the result is NULL.

Improve a report label

A dashboard usually should not show a blank region label without context. You can display a meaningful label:

SELECT
    order_id,
    COALESCE(region, 'Unknown') AS reporting_region
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

This changes the query output only. It does not update the underlying region column.

Use a sensible fallback sequence

Sometimes there are multiple possible sources for a value:

SELECT
    customer_id,
    COALESCE(
        primary_email,
        secondary_email,
        'No email available'
    ) AS contact_email
FROM PORTFOLIO_DB.RAW.CUSTOMER;

This applies a documented preference order:

1. Use primary_email when available.
2. Otherwise use secondary_email.
3. Otherwise show a clear final label.

The values should be compatible types. For instance, use text fallbacks for text columns and numeric fallbacks for numeric columns.

Use zero only when zero is the business meaning

COALESCE is especially useful before arithmetic, because arithmetic with NULL usually produces NULL.

SELECT
    order_id,
    quantity * unit_price - COALESCE(discount_amount, 0) AS net_revenue
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

This is correct only if a missing discount_amount means “no discount was applied.”

If NULL means “discount amount unknown,” replacing it with zero would make the report look more certain than the source data actually is. In that case, preserve the uncertainty:

SELECT
    order_id,
    CASE
        WHEN discount_amount IS NULL THEN 'Needs discount review'
        ELSE 'Discount recorded'
    END AS discount_data_status
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

The general rule is:

Situation	Appropriate approach
NULL means “not yet known”	Preserve it and report or investigate it.
NULL means “not applicable”	Keep it or label it clearly.
NULL is contractually equivalent to zero	Use COALESCE(column, 0) in the calculation.
A report needs a readable label	Use COALESCE(column, 'Unknown') in the output.
A required field is missing	Find it with IS NULL and treat it as a QA exception.

---

A compact NULL audit for reporting QA

Before trusting a KPI or dashboard, profile the important fields. COUNT(*) counts all rows, while COUNT(column_name) counts only rows where that column is not NULL.

SELECT
    COUNT(*) AS total_orders,
    COUNT(customer_id) AS orders_with_customer_id,
    COUNT(*) - COUNT(customer_id) AS orders_missing_customer_id,
    COUNT(order_status) AS orders_with_status,
    COUNT(*) - COUNT(order_status) AS orders_missing_status
FROM PORTFOLIO_DB.RAW.SALES_ORDER;

This is a compact way to identify whether missing data could affect a report.

For example:

• If customer_id is missing, a customer-level metric may undercount when tables are joined later.
• If order_status is missing, filters such as order_status <> 'CANCELLED' may silently omit records.
• If discount_amount is missing, substituting zero could alter net-revenue totals.

Keep raw data quality separate from presentation choices. A reporting view may display 'Unknown' through COALESCE, while a QA query should still measure how many original values were actually NULL.

---

Key takeaways

NULL represents missing, unknown, or inapplicable information. It is not zero, an empty string, or a normal value to compare with =.

The core patterns are:

-- Find missing values
WHERE column_name IS NULL;

-- Keep only populated values
WHERE column_name IS NOT NULL;

-- Display or calculate with a fallback
COALESCE(column_name, fallback_value);

-- Explicitly retain missing values in an otherwise negative filter
WHERE column_name <> 'CANCELLED'
   OR column_name IS NULL;

Most importantly, WHERE returns only TRUE rows. Conditions involving missing values often produce UNKNOWN, causing rows to disappear unless you explicitly account for them.

Next, you will summarize data with COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING. The NULL habits from this lesson will matter immediately, because aggregate functions do not all treat missing values in the same way.