SQL NULL: Working with missing and unknown values correctly

The following orders table contains six orders. Some have a discount code and a shipping time, while others have NULL in those columns. We will use this table for the order examples below:

Order 1002 is pending, and its shipped_at is NULL. This may mean the order has not shipped yet. Order 1004 is cancelled, and its shipped_at is also NULL. This order may never need a shipping time. Both orders have NULL, but possibly for different reasons. NULL only tells us a value is missing or unknown, not the reason behind.

Order 1005 has '' in discount_code. This is an empty string, meaning text with no characters. In these examples, NULL, '', and 'NULL' are different:

• NULL means no value is given.
• '' is an empty text value.
• 'NULL' is a text value containing the four letters NULL.

Suppose we want to find the orders that used the discount code 'SAVE10'. We can write the same kind of equality comparison that we used in P01:

SELECT *
FROM orders
WHERE discount_code = 'SAVE10';

• For order 1001, discount_code contains 'SAVE10', so the condition is TRUE.
• For order 1003, it contains 'WELCOME', so the condition is FALSE.
• For order 1002, however, discount_code is NULL. SQL has no way of knowing what that missing value represented by NULL is and how to compare it with 'SAVE10'. So it cannot give a definite TRUE or FALSE answer. Hence, the comparison returns UNKNOWN.

This comparison matters because WHERE filters a row only when its condition is TRUE, and it leaves out rows whose condition is FALSE and rows whose condition is UNKNOWN. The query therefore returns orders 1001 and 1006, the two orders with a given 'SAVE10' code.

Three-valued logic

As can be seen from the above table, a comparison operation can return three different values - TRUE, FALSE and UNKNOWN - SQL calls these three possible answers three-valued logic. This is because an ordinary comparison with NULL cannot establish an answer, so it returns UNKNOWN rather than TRUE or FALSE.

The same rule applies to <>, >, <, >=, and <=. For example, NULL > 100 is UNKNOWN because the missing number might be above 100 or below it. Even NULL = NULL and NULL <> NULL are UNKNOWN: two missing values do not tell us whether their values would be equal or different.

If you run these comparisons directly, your SQL tool will usually display NULL for the unknown result. We use the word UNKNOWN when explaining the logic:

SELECT 120 > 100;    -- TRUE
SELECT 80 > 100;     -- FALSE
SELECT NULL > 100;   -- NULL, meaning UNKNOWN
SELECT NULL = NULL;  -- NULL, meaning UNKNOWN
SELECT NULL <> NULL; -- NULL, meaning UNKNOWN

Now that we know why = cannot compare a missing value, we can choose the correct way to search for one.

Suppose we need orders that have no given shipping time. It is tempting to use = NULL, just as we used = 'SAVE10' for a discount code:

-- This query does not find the missing shipping times.
SELECT
    order_id,
    customer,
    shipped_at
FROM orders
WHERE shipped_at = NULL;

For an order with a given shipping time, comparing that time with NULL returns UNKNOWN. For an order with no shipping time, the comparison becomes NULL = NULL, which also returns UNKNOWN. Since no row produces TRUE, this query returns no rows.

To correctly check whether a value is missing in SQL, use IS NULL. Unlike = NULL, this condition tests whether the column contains NULL and gives a definite TRUE or FALSE answer:

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

• For order 1002, shipped_at IS NULL is TRUE, so the row is included.
• For order 1001, a shipping time is given, so shipped_at IS NULL is FALSE and the row is excluded.

Notice that the three returned orders have different statuses. The query finds orders without a given shipping time.

However, if we want orders with a given shipping time instead, use IS NOT NULL:

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

These two conditions let us choose rows based on whether a value is given or not. Use IS NULL to find rows where a value is missing. Use IS NOT NULL to find rows where a value is given.

We can now return to the discount codes example with a more specific requirement:

The requirement explicitly includes orders whose discount_code is NULL. We therefore expect orders 1002, 1003, 1004, and 1005. A first attempt might use <> to exclude 'SAVE10', but let us check what that query actually does.

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

Choose the corrected condition

The query needs to allow a value different from 'SAVE10' or a missing value.

-- 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'

How AND, OR, and NOT handle UNKNOWN

The corrected filter works because OR can give a definite answer even when one part is UNKNOWN. AND works differently: every part must be TRUE. For example, TRUE AND UNKNOWN is UNKNOWN, while FALSE AND UNKNOWN is FALSE because one false condition is already enough to fail an AND condition.

Use the same reasoning for numbers

The question about missing values also applies to discount_amount. If we want confirmed discounts above 100, WHERE discount_amount > 100 is enough. It returns orders 1001, 1003, and 1006; missing discount amounts do not pass that comparison.

Suppose instead that the requirement asks for discounts of 100 or less, including orders with no discount. Then we write both conditions:

WHERE discount_amount <= 100
   OR discount_amount IS NULL

This returns orders 1002, 1004, and 1005. Writing only WHERE discount_amount <= 100 would return no rows from this table. The choice depends on the requirement.

So far, we have used conditions in WHERE to decide which rows to return. Sometimes the requirement might be different: keep the rows, but display a useful value where a column is NULL. For example, we may want to show 'No discount' instead of a missing discount code.

To implement this, we can use COALESCE in the SELECT part of the query:

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

COALESCE(discount_code, 'No discount') returns discount_code when it is not NULL. When it is NULL, it returns 'No discount' instead. Also, using AS and then some text, as in AS displayed_discount, gives the resulting column a name.

• For order 1001, the first value is 'SAVE10', so COALESCE returns it.
• For order 1002, the first value is NULL, so COALESCE returns the second value, 'No discount'.
• Order 1005 still shows '': an empty string is not NULL in these examples, so it is returned as it is.

The values passed to COALESCE must have types that the database can use together. In our display example, both the discount code and its replacement are text. For a numeric calculation, use a numeric replacement such as 0.

Using COALESCE in a calculation

Suppose we want to calculate an order amount by multiplying the quantity by the unit price, then subtracting the discount:

quantity * unit_price - discount_amount

• For order 1001, the calculation is 2 * 750 - 150, which gives 1350.
• For order 1002, it is 1 * 3200 - NULL, which gives NULL. We know the quantity and price, but the missing discount means the expression cannot establish the final amount.

If we know that a missing discount_amount means no discount was applied, we can use COALESCE(discount_amount, 0) to subtract zero in those rows:

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

Order 1002 now has a calculated amount of 3200 because its missing discount is treated as zero. The other missing discounts are handled in the same way.

Our display query still leaves order 1005 with an empty discount code. We cannot fix that by adding another replacement to COALESCE, because COALESCE already finds a given value: ''. If the requirement is that an empty value should mean the same thing as a missing value, then we first need to turn '' into NULL. NULLIF(value1, value2) does that. It returns NULL if value1 = value2; otherwise, it returns value1.

For our discount codes, the expression is NULLIF(discount_code, ''):

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

• For order 1005, NULLIF('', '') returns NULL because the two empty strings compare equal.
• For order 1001, NULLIF('SAVE10', '') returns 'SAVE10' because the values are different.

An existing NULL also remains NULL in the result. Now both forms of missing code produce NULL, so we can use the same COALESCE replacement for them:

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

We have seen how NULL affects = and <>. The same comparisons are involved when we check a list of values with IN or NOT IN. To see this, return to the orders table and find orders whose discount code is either 'SAVE10' or 'WELCOME':

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

Orders 1001, 1003, and 1006 are returned because their given codes match one of the values in the list. Orders with a NULL code are excluded because SQL cannot establish a match. The empty code in order 1005 is also excluded, but for a different reason: '' is a value that matches neither code.

For this two-value list, discount_code IN ('SAVE10', 'WELCOME') has the same comparison meaning as:

WHERE discount_code = 'SAVE10'
   OR discount_code = 'WELCOME'

If discount_code is NULL, both comparisons return UNKNOWN. Combining them with OR still gives UNKNOWN, so the row is excluded.

NOT IN does not include the row with a missing code. A comparison with NULL produces UNKNOWN. Applying NOT to UNKNOWN still gives UNKNOWN, so the row is excluded.

Use this Orders table for Pause and Predict question 3 given below:

SELECT order_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

In the previous section, a missing value in a row caused that row to be excluded. There is a second situation to watch for: a NULL value inside the list used by NOT IN. That can prevent every row from passing the condition, including rows with non-null values.

Consider this condition on the original orders table:

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

To understand the problem, imagine an order with the code 'SPRING20'. For 'SPRING20' to be outside the list, it must differ from every listed value. SQL must therefore establish all three comparisons:

The NOT IN comparisons are combined with AND. The first two are TRUE, but the last is UNKNOWN, so TRUE AND TRUE AND UNKNOWN is UNKNOWN. Hence, the row cannot pass the WHERE condition. We can check the same result directly (Remember we can write any expression in the SELECT clause):

SELECT 'SPRING20' NOT IN ('SAVE10', 'WELCOME', NULL);
-- NULL, meaning UNKNOWN

A discount code already in the list behaves differently. For example, 'SAVE10' NOT IN ('SAVE10', 'WELCOME', NULL) is FALSE because there is a definite match. Both FALSE and UNKNOWN are excluded by WHERE, so this list containing NULL returns no row.

Using the same problem with a subquery

A list is not always written directly into the query. It can come from another SELECT, called a subquery. Suppose we have these two tables and want customers who have no matching ID in blocked_customers.

We expect Asha and Carla because only Ben’s ID, 2, is listed as a known blocked ID. The following query appears to express that requirement.

SELECT customer_id, customer
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

Use NOT EXISTS to find whether a matching row exists

Our requirement is to return a customer if no row in blocked_customers has the same ID as that in the Customers table. NOT EXISTS can be used to do it in a cleaner way:

SELECT c.customer_id, c.customer
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers AS b
    WHERE b.customer_id = c.customer_id
);

Inside EXISTS, SELECT 1 is a common way to write the query because we only need to know whether any matching row is found; we do not need its selected value.

For Asha, the inner WHERE condition looks for a blocked ID equal to 1. Neither 2 = 1 nor NULL = 1 gives TRUE, so the inner query finds no matching row. NOT EXISTS is therefore TRUE, and Asha is included. For Ben, 2 = 2 is TRUE, so a matching row exists and Ben is excluded. Carla has no matching blocked ID and is included.

The NULL in blocked_customers does not match any known customer ID, so it does not remove unrelated customers from this result.

Another option: remove NULL before using NOT IN

For the same three customers, we can also filter missing IDs out of the subquery:

SELECT customer_id, customer
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customers
    WHERE customer_id IS NOT NULL
);

The subquery now returns only 2, so the outer query returns Asha and Carla. Both corrected queries work for these tables because every ID in customers is given.

What if a customer also has a missing ID?

Until now, every row in customers had a given customer_id. Let us add Dev, whose ID is missing, and keep blocked_customers unchanged.

We still want customers with no matching blocked ID. But for Dev, we also need to decide what a missing customer ID should mean.

NOT EXISTS includes Dev when the comparison uses =

Consider the same NOT EXISTS query:

SELECT c.customer_id, c.customer
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers AS b
    WHERE b.customer_id = c.customer_id
);

For Dev, c.customer_id is NULL. The inner query checks whether either blocked ID matches that missing ID:

Neither comparison produces TRUE, so neither blocked row passes the inner WHERE condition. The inner query therefore returns no matching rows.

EXISTS is FALSE when its query returns no rows, so NOT EXISTS is TRUE. Dev is included:

NOT IN excludes Dev even after we remove missing blocked IDs

Now use the corrected NOT IN query from the previous example, which removes NULL from the blocked-ID list:

SELECT customer_id, customer
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM blocked_customers
    WHERE customer_id IS NOT NULL
);

The subquery returns only 2. For Asha and Carla, the comparison is straightforward: their IDs are different from 2, so they are included. Ben’s ID matches 2, so he is excluded.

For Dev, however, the condition becomes: NULL NOT IN (2)

That condition is UNKNOWN, because SQL cannot establish whether the missing ID differs from 2. The outer WHERE therefore excludes Dev:

Removing NULL from blocked_customers fixes the problem inside the list. It does not change how NOT IN handles a missing value in customers.customer_id.

Explicitly exclude customers whose IDs are missing

Suppose the requirement says: “Return customers with a given ID who have no matching blocked ID.”

We can express both parts using IS NOT NULL and NOT EXISTS:

SELECT c.customer_id, c.customer
FROM customers AS c
WHERE c.customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM blocked_customers AS b
      WHERE b.customer_id = c.customer_id
  );

Dev now fails c.customer_id IS NOT NULL, so he is excluded even though the NOT EXISTS condition is TRUE. Asha and Carla satisfy both conditions and are returned:

The query now states the requirement directly: the customer must have a given ID, and no blocked row may match that ID.

Most examples so far have treated NULL as an unknown value. Sometimes, however, a requirement asks whether two fields have the same given value, treating two missing fields as the same. For example, when comparing an old discount code with a new discount code, we may want two missing codes to count as unchanged.

As we saw earlier, NULL = NULL returns UNKNOWN. In DuckDB and PostgreSQL, IS NOT DISTINCT FROM gives the definite equality result needed for this requirement:

SELECT NULL = NULL;                   -- NULL, meaning UNKNOWN
SELECT NULL IS NOT DISTINCT FROM NULL; -- TRUE
SELECT NULL IS NOT DISTINCT FROM 'SAVE10'; -- FALSE

With two given values, IS NOT DISTINCT FROM behaves like =. With two NULL values, it returns TRUE. With one given value and one NULL, it returns FALSE. This is called NULL-safe equality because the comparison gives a definite answer even when a value is missing.

The corresponding difference test is IS DISTINCT FROM. It returns TRUE when the given values differ, or when one is missing and the other is given:

Use old_code IS NOT DISTINCT FROM new_code to check whether two values are equal, treating two NULL values as equal. Use old_code IS DISTINCT FROM new_code to check whether they differ, treating one NULL and one non-NULL value as different.

Use this rule only when it fits the question. Two missing discount-code fields may reasonably mean “unchanged,” but two missing customer IDs do not prove that they belong to the same customer.

The examples all come back to the same decision: what should happen to a row when one of its values is missing? If you need to find the missing value itself, use IS NULL. If you are comparing it with another value, remember that an ordinary comparison returns UNKNOWN, which does not pass a WHERE condition. When the requirement says to include missing values, write that choice explicitly with a condition such as OR discount_code IS NULL.

COALESCE can replace a missing value with a fallback. NULLIF can turn a specific value, such as '', into NULL.

If a query leaves out more rows than expected, work through one row with a missing value. Check the result of each comparison, then combine those results using AND, OR, or NOT. This lets you explain why the row was excluded and whether the filter condition needs to change.

1. NULL means a value is missing or unknown. It does not tell us why the value is missing.
2. A comparison with NULL, even NULL = NULL, returns UNKNOWN, and WHERE keeps only rows whose condition is TRUE.
3. Use IS NULL and IS NOT NULL to check for missing values. When the requirement includes missing values, add OR column_name IS NULL.
4. COALESCE replaces a missing value with a fallback, and NULLIF turns a specific value, such as '', into NULL.
5. One NULL in a NOT IN list makes the condition UNKNOWN for every row that is not in the list. Use NOT EXISTS, or remove NULL from the list first.

In this pattern, we learned how NULL affects comparisons, filters, and calculations. We used IS NULL and IS NOT NULL to find missing values, COALESCE and NULLIF to handle them in results, and NOT EXISTS to avoid the NOT IN trap.

In P03, we will use ORDER BY, ASC, DESC, and LIMIT to control the order and number of rows returned. You now have the rules needed to decide which rows should reach that result in the first place.