SQL NULL: working with missing and unknown values correctly

What NULL means

Real-world data is rarely complete.

An order may not have shipped yet. A customer may not have used a discount code. A cancellation reason may not apply to an order that was completed successfully.

Relational databases need a way to represent these situations. SQL uses NULL.

Consider the following orders table:

Several columns contain NULL. For order 1002, shipped_at = NULL: the order is still pending, so no shipping time has been recorded. For order 1004, shipped_at = NULL: the order was cancelled, so a shipping time may never exist. For discount_code, NULL means there is no known discount code stored for that order.

Why NULL behaves differently

The most important rule about NULL is:

Consider:

SELECT *
FROM orders
WHERE discount_code = 'SAVE10';

SQL asks: is the discount code equal to SAVE10? For order 1001, SAVE10 = SAVE10 is TRUE. For order 1003, WELCOME = SAVE10 is FALSE. But order 1002 contains discount_code = NULL, so SQL evaluates NULL = SAVE10. The result is neither TRUE nor FALSE. It is UNKNOWN.

SQL therefore works with three logical results, TRUE, FALSE, and UNKNOWN. This is called three-valued logic.

Three-valued logic

UNKNOWN appears when SQL cannot determine whether a comparison involving NULL is true or false. For example:

120  > 100    → TRUE
80   > 100    → FALSE
NULL > 100    → UNKNOWN

SQL does not know what the missing value is. It could be 50. It could be 150. Without knowing the value, SQL cannot determine whether the comparison is true or false.

WHERE keeps only TRUE

This is where NULL starts affecting query results. A WHERE clause keeps a row only when its complete condition evaluates to TRUE.

So a condition does not need to be FALSE for a row to disappear. UNKNOWN rows are also excluded. This explains many SQL bugs involving NULL: the query runs successfully, but some rows silently disappear from the result.

UNKNOWN with AND and OR

Three-valued logic also affects compound conditions. You do not need to memorise every combination immediately. The important idea is that UNKNOWN can propagate through a larger condition. For example, TRUE AND UNKNOWN is UNKNOWN, but TRUE OR UNKNOWN is TRUE. A useful summary is:

Comparing NULL with = does not work

Suppose we want orders that have no shipping time. A natural first attempt might be:

SELECT
    order_id,
    customer,
    shipped_at
FROM orders
WHERE shipped_at = NULL;

You might expect orders 1002, 1004, and 1006. But the query does not behave that way. For order 1002, SQL evaluates NULL = NULL. The result is UNKNOWN, not TRUE, so the row is excluded. The same happens for every other row. Even NULL = NULL does not evaluate to TRUE: SQL knows only that both values are missing. It does not know whether the underlying values, if they existed, would actually be equal. For the same reason, NULL <> NULL also evaluates to UNKNOWN.

Use IS NULL

SQL provides a special condition for checking whether a value is missing: IS NULL. To find orders that do not have a shipping time:

SELECT
    order_id,
    customer,
    shipped_at,
    status
FROM orders
WHERE shipped_at IS NULL
ORDER BY order_id;

For order 1002, shipped_at IS NULL evaluates to TRUE. For order 1001, shipped_at IS NULL evaluates to FALSE.

Use IS NOT NULL

The opposite condition is IS NOT NULL. Suppose we need orders for which a shipping time has been recorded:

SELECT
    order_id,
    customer,
    shipped_at,
    status
FROM orders
WHERE shipped_at IS NOT NULL
ORDER BY order_id;

The rule is: column IS NULL checks for missing values, column IS NOT NULL checks for existing values. Do not use column = NULL or column <> NULL for these tests.

The filter that silently drops NULL rows

Consider this requirement:

A natural query might be:

The query looks reasonable. The requirement says “discount code is not SAVE10,” and the query says discount_code <> 'SAVE10'. But the query does not return all the rows we want.

SELECT
    order_id,
    customer,
    discount_code
FROM orders
WHERE discount_code <> 'SAVE10'
ORDER BY order_id;

SQL evaluates the condition for each row:

The fix

The requirement has two ways for an order to qualify:

• The discount code is known and is not SAVE10.
• There is no discount code recorded.

So write both conditions:

SELECT
    order_id,
    customer,
    discount_code
FROM orders
WHERE discount_code <> 'SAVE10'
   OR discount_code IS NULL
ORDER BY order_id;

Now the SQL matches the requirement.

Nullable comparisons

Whenever a nullable column appears in a comparison such as =, <>, >, <, >=, or <=, ask:

Consider WHERE discount_amount > 100. For a NULL discount amount, NULL > 100 evaluates to UNKNOWN, and the row is excluded. If the requirement is “show orders with discounts above 100,” then that behaviour may be correct.

But consider a different requirement: “show orders where the discount is 100 or less, including orders with no discount.” This is incomplete, because NULL rows evaluate to UNKNOWN:

WHERE discount_amount <= 100

If the business requirement says they should also qualify, write:

WHERE discount_amount <= 100
   OR discount_amount IS NULL

The correct SQL depends on what the missing value means for the question being answered.

Providing fallback values with COALESCE

Sometimes you do not want to remove rows containing NULL. You want to display or calculate another value instead. SQL provides COALESCE. The basic form is COALESCE(value1, value2, ...), and it returns the first value that is not NULL.

For the next example, assume the business has confirmed that a NULL discount_code means no discount code was used. We can write:

SELECT
    order_id,
    customer,
    discount_code,
    COALESCE(discount_code, 'No discount') AS displayed_discount
FROM orders
ORDER BY order_id;

For order 1001, COALESCE('SAVE10', 'No discount') returns SAVE10 because the first value is already non-NULL. For order 1002, COALESCE(NULL, 'No discount') returns No discount because SQL moves to the next value. COALESCE can also contain several fallback values: COALESCE(value1, value2, value3, 'Unknown'). SQL checks the values from left to right and returns the first non-NULL one.

COALESCE in calculations

NULL also affects arithmetic. Suppose we calculate quantity * unit_price - discount_amount. For order 1001, 2 × 750 - 150 = 1350. But for order 1002, 1 × 3200 - NULL is NULL. If the business rule confirms that a missing discount_amount means no discount was applied, we can treat the missing value as zero:

SELECT
    order_id,
    quantity * unit_price AS gross_amount,
    discount_amount,
    quantity * unit_price
        - COALESCE(discount_amount, 0) AS final_amount
FROM orders
ORDER BY order_id;

For this calculation, COALESCE(discount_amount, 0) turns a missing discount amount into zero.

Gotcha: COALESCE does not decide what NULL means

The expression COALESCE(discount_amount, 0) is easy to write. Whether it is correct depends on what NULL means. If NULL means “no discount was applied,” then replacing it with 0 makes sense. But suppose NULL means “the discount calculation has not completed yet.” Replacing it with zero changes unknown into known to be zero. Those are different statements. COALESCE can replace a missing value. It cannot determine whether that replacement is correct for the business.

Gotcha: COALESCE only replaces NULL

Look at order 1005. Its discount code is an empty string ''. Now consider COALESCE(discount_code, 'No discount'). The result for order 1005 is still an empty string. Why? Because an empty string is not NULL in many commonly used SQL systems. COALESCE asks whether the value is NULL; if the value is an empty string, it is returned as-is. This distinction becomes important when working with data imported from files, APIs, forms, and older applications.

Turning special values into NULL with NULLIF

Sometimes source data uses an ordinary value to represent missing information. Our discount_code column contains an empty string for order 1005. Suppose the business wants an empty discount code to be treated as missing. SQL provides NULLIF. The basic form is NULLIF(value1, value2). It means: if value1 equals value2, return NULL; otherwise return value1. So NULLIF(discount_code, '') means: if discount_code is an empty string, return NULL, otherwise keep the original value.

SELECT
    order_id,
    discount_code,
    NULLIF(discount_code, '') AS normalized_discount_code
FROM orders
ORDER BY order_id;

For order 1005, NULLIF('', '') returns NULL. For order 1001, NULLIF('SAVE10', '') returns SAVE10 because the values are different.

NULLIF and COALESCE work well together

Suppose our source data represents a missing discount code in two ways, NULL and ''. We want the report to display No discount for both. First, convert the empty string into NULL with NULLIF(discount_code, ''). Then provide a fallback with COALESCE(...):

SELECT
    order_id,
    customer,
    COALESCE(
        NULLIF(discount_code, ''),
        'No discount'
    ) AS discount
FROM orders
ORDER BY order_id;

NULLIF:   chosen value → NULL
COALESCE: NULL → fallback value

This combination is useful when source data uses special values to represent missing information.

NULL with IN and NOT IN

NULL and IN

NULL also affects list comparisons. Consider WHERE discount_code IN ('SAVE10', 'WELCOME'). For discount_code = SAVE10 the condition is TRUE; for discount_code = OTHER it is FALSE; for discount_code = NULL it is UNKNOWN, and the NULL row is excluded. Matching non-NULL values still work normally. So:

SELECT
    order_id,
    discount_code
FROM orders
WHERE discount_code IN ('SAVE10', 'WELCOME');

still returns rows containing SAVE10 and WELCOME. NULL values in the column do not cause all matching rows to disappear.

The dangerous case: NOT IN and NULL

NOT IN requires more care. Consider WHERE discount_code NOT IN ('SAVE10', 'WELCOME'). For an ordinary value such as SPRING20, SQL can determine that the value is not in the list. But for NULL the result is UNKNOWN, so the row is excluded. There is a more dangerous situation when the list itself contains NULL:

WHERE discount_code NOT IN (
    'SAVE10',
    'WELCOME',
    NULL
)

Consider discount_code = SPRING20. Conceptually, SQL must establish:

SPRING20 <> 'SAVE10'    → TRUE
SPRING20 <> 'WELCOME'   → TRUE
SPRING20 <> NULL        → UNKNOWN

TRUE AND TRUE AND UNKNOWN → UNKNOWN  → row excluded

How to spot NULL problems in your own SQL

Look for these signs:

Whenever one of these happens, inspect the nullable columns and ask how the missing values should behave.

Missing information means different things

The hardest part of NULL is not the syntax. The syntax is small: IS NULL, IS NOT NULL, COALESCE(...), NULLIF(...). The harder question is: what does missing information mean for this particular business requirement?

Consider shipped_at = NULL. For a pending order, that may mean “not shipped yet.” For a cancelled order, it may mean “shipping does not apply.” For a newly imported order, it could mean “the shipping information has not arrived yet.”

One more prediction

Consider the same orders table:

Requirement: return orders that did not use SAVE10, including orders where no discount code was recorded. Which query correctly expresses the requirement?

-- Option A
WHERE discount_code <> 'SAVE10'

-- Option B
WHERE discount_code = NULL
   OR discount_code <> 'SAVE10'

-- Option C
WHERE discount_code <> 'SAVE10'
   OR discount_code IS NULL

-- Option D
WHERE NOT discount_code = 'SAVE10'

Five ideas to lock in

Summary

NULL represents the absence of a known value. It behaves differently from ordinary values because comparisons involving NULL usually evaluate to UNKNOWN. A WHERE clause keeps only rows where the final condition is TRUE, so NULL values can silently cause rows to disappear.

Next, we will learn how to control the order and number of rows returned by a query using ORDER BY, ASC, DESC, and LIMIT.