Null Semantics in SQL

NULL Is Not a Value

NULL is not zero, not an empty string, not FALSE. NULL means "unknown" or "not yet recorded." It is a marker for missing information, and it behaves differently from every other value in SQL.

The most important consequence: any comparison with NULL returns UNKNOWN, not TRUE or FALSE.

-- All of these return UNKNOWN, not TRUE or FALSE
SELECT NULL = NULL;        -- UNKNOWN
SELECT NULL != NULL;       -- UNKNOWN
SELECT NULL > 5;           -- UNKNOWN
SELECT NULL = 0;           -- UNKNOWN

UNKNOWN is the third value in SQL's three-valued logic (3VL). A WHERE clause keeps only rows where the condition evaluates to TRUE. Rows where the condition is FALSE or UNKNOWN are discarded.

How NULL Propagates

NULL propagates through expressions. If any operand is NULL, the result is NULL (with a few exceptions like COALESCE).

-- Arithmetic with NULL
SELECT 5 + NULL;           -- NULL
SELECT NULL * 100;         -- NULL
SELECT 1 / NULL;           -- NULL

-- String concatenation with NULL
SELECT 'Hello' || NULL;    -- NULL (in PostgreSQL)

-- Comparison with NULL
SELECT NULL = NULL;        -- UNKNOWN
SELECT NULL < 10;          -- UNKNOWN

-- Boolean logic with NULL
SELECT TRUE AND NULL;      -- UNKNOWN
SELECT TRUE OR NULL;       -- TRUE (TRUE OR anything is TRUE)
SELECT FALSE AND NULL;     -- FALSE (FALSE AND anything is FALSE)
SELECT FALSE OR NULL;      -- UNKNOWN
SELECT NOT NULL;           -- UNKNOWN

The last four rows show the subtlety of 3VL. TRUE OR NULL is TRUE because if one side of an OR is TRUE, the result is TRUE regardless of the other side. FALSE AND NULL is FALSE because if one side of an AND is FALSE, the result is FALSE regardless of the other side. But TRUE AND NULL and FALSE OR NULL depend on the NULL, so they are UNKNOWN.

The WHERE Clause Filter

WHERE keeps only TRUE rows. This is the source of the most common NULL bug:

-- Intended: all orders that are NOT cancelled
SELECT order_id, status
FROM orders
WHERE status != 'cancelled';

If any row has status = NULL, that row is excluded. NULL != 'cancelled' is UNKNOWN, not TRUE. The row silently disappears from the result.

The fix:

SELECT order_id, status
FROM orders
WHERE status != 'cancelled'
   OR status IS NULL;

Or equivalently:

WHERE COALESCE(status, '') != 'cancelled';

NULL in JOIN Conditions

NULL values never match in a JOIN. Two rows with NULL in the join key are not considered equal.

-- If customer_id is NULL in both tables, these rows will NOT join
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

This is usually the desired behavior (you do not want to match unknown customers), but it means NULL join keys silently drop rows from inner joins. Outer joins are the tool for preserving those rows — covered in the joins topic.

Why = NULL Never Works

The most common SQL mistake is using = to test for NULL:

-- This returns zero rows, always
SELECT *
FROM orders
WHERE ship_date = NULL;

ship_date = NULL evaluates to UNKNOWN for every row, and WHERE keeps only TRUE rows. The query runs without error but returns nothing. This is a silent failure — no error message, no warning, just an empty result that looks like "no data" instead of "wrong query."

The Correct Syntax

Use IS NULL and IS NOT NULL:

-- Orders that have not shipped yet
SELECT order_id, order_date
FROM orders
WHERE ship_date IS NULL;

-- Orders that have shipped
SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NOT NULL;

IS NULL is a predicate, not a comparison. It returns TRUE or FALSE, never UNKNOWN. It is the only correct way to test for NULL.

IS NULL vs IS NOT NULL

IS NOT NULL is the exact negation of IS NULL. Every row is either NULL or not NULL — there is no UNKNOWN case for the IS NULL predicate itself.

-- These two queries return complementary result sets
SELECT COUNT(*) FROM orders WHERE ship_date IS NULL;
SELECT COUNT(*) FROM orders WHERE ship_date IS NOT NULL;
-- The sum equals SELECT COUNT(*) FROM orders;

This is useful for data quality checks. If the sum does not equal the total row count, something is wrong with the data or the query.

NULL in Boolean Expressions

When NULL appears in a boolean expression, the result depends on the other operands:

-- If is_active is NULL, the result is UNKNOWN (row excluded)
WHERE is_active = TRUE;

-- To include NULL as "not active":
WHERE is_active = TRUE OR is_active IS NULL;

-- To treat NULL as FALSE:
WHERE COALESCE(is_active, FALSE) = TRUE;

The COALESCE approach is cleaner when you want to treat NULL as a default value. The IS NULL approach is more explicit about the intent.

NULL in IN and NOT IN

IN with NULL in the list has the same trap as NOT IN:

-- If the subquery returns NULL, this returns zero rows
WHERE customer_id NOT IN (SELECT customer_id FROM blocked_customers);

-- Fix: filter NULLs from the subquery
WHERE customer_id NOT IN (
    SELECT customer_id FROM blocked_customers WHERE customer_id IS NOT NULL
);

IN with NULL in the list is less dangerous because x IN (1, 2, NULL) returns TRUE if x matches 1 or 2, and UNKNOWN if x matches neither. The NULL element does not poison the entire expression the way it does for NOT IN.

COALESCE: The NULL Replacement

COALESCE(a, b, c, ...) returns the first non-NULL argument. It is the standard tool for handling NULL in computed fields and filters.

-- Treat NULL discount as zero
SELECT
    order_id,
    quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100) AS net_revenue
FROM orders;

-- Use a default value for NULL customer names
SELECT
    order_id,
    COALESCE(customer_name, 'Unknown') AS customer_name
FROM orders;

-- Chain multiple fallbacks
SELECT
    COALESCE(mobile_phone, work_phone, email, 'No contact') AS primary_contact
FROM customers;

COALESCE is variadic — it accepts any number of arguments. It evaluates left to right and stops at the first non-NULL value. All arguments after the first non-NULL are not evaluated.

COALESCE in Filters

COALESCE can make filters NULL-safe:

-- Find orders with a discount, treating NULL as 0
SELECT order_id, discount_pct
FROM orders
WHERE COALESCE(discount_pct, 0) > 0;

-- Find orders in a region, treating NULL as 'Unassigned'
SELECT order_id, region
FROM orders
WHERE COALESCE(region, 'Unassigned') = 'East';

The pattern is: use COALESCE to convert NULL to a default value, then apply the normal comparison. This avoids the UNKNOWN trap.

NULLIF: The Inverse of COALESCE

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It converts a specific sentinel value to NULL.

-- Avoid division by zero: if denominator is 0, return NULL
SELECT
    order_id,
    total_amount / NULLIF(quantity, 0) AS avg_price
FROM orders;

-- Convert empty strings to NULL
SELECT
    customer_id,
    NULLIF(email, '') AS email
FROM customers;

NULLIF(x, 0) is the standard guard against division by zero. Without it, total_amount / 0 raises an error in most databases. With it, the result is NULL, which is usually the desired behavior for "undefined."

COALESCE + NULLIF Together

The two functions complement each other. NULLIF converts a bad value to NULL; COALESCE replaces NULL with a default:

-- Convert empty string to NULL, then replace NULL with 'Unknown'
SELECT COALESCE(NULLIF(city, ''), 'Unknown') AS city
FROM customers;

-- Guard division, then replace NULL result with 0
SELECT COALESCE(total_amount / NULLIF(quantity, 0), 0) AS avg_price
FROM orders;

This pattern — NULLIF to sanitize input, COALESCE to provide a default — is the standard approach for robust analytical SQL.

NULLIF vs CASE

NULLIF(a, b) is equivalent to CASE WHEN a = b THEN NULL ELSE a END. The NULLIF form is shorter and clearer. Use NULLIF for the specific "convert this value to NULL" case; use CASE for more complex conditional logic.

How Aggregates Treat NULL

Aggregate functions handle NULL differently. Understanding the difference is critical for correct counts and sums.

COUNT(*) vs COUNT(column)

This is the most important distinction in analytical SQL:

-- Total number of orders (including orders with NULL customer_id)
SELECT COUNT(*) FROM orders;

-- Number of orders with a known customer
SELECT COUNT(customer_id) FROM orders;

If 100 orders exist and 10 have NULL customer_id, COUNT(*) returns 100 and COUNT(customer_id) returns 90. Both are correct answers to different questions. COUNT(*) answers "how many rows?" COUNT(customer_id) answers "how many rows have a known customer?"

SUM and AVG with NULL

SUM and AVG ignore NULLs. This is usually the desired behavior — you want to sum the known values, not treat NULL as zero.

-- Sum of known discounts (NULLs ignored)
SELECT SUM(discount_pct) FROM orders;
-- If discounts are 10, NULL, 20, the result is 30

-- Average of known discounts
SELECT AVG(discount_pct) FROM orders;
-- If discounts are 10, NULL, 20, the result is 15 (not 10)

AVG divides by the count of non-NULL values, not the total row count. This is correct for "average of known values" but can be misleading if you expect "average treating NULL as zero."

The All-NULL Case

If every value in a column is NULL, SUM and AVG return NULL (not zero):

SELECT SUM(discount_pct) FROM orders WHERE discount_pct IS NULL;
-- Returns NULL, not 0

This is a common source of bugs in reports. A NULL sum displays as blank or NULL in a dashboard, which may be misinterpreted as "no data" instead of "all values are missing." Use COALESCE to provide a default:

SELECT COALESCE(SUM(discount_pct), 0) AS total_discount
FROM orders;

COUNT(DISTINCT column) and NULL

COUNT(DISTINCT column) counts unique non-NULL values. NULLs are excluded:

-- Count of unique non-NULL regions
SELECT COUNT(DISTINCT region) FROM orders;
-- If regions are 'East', 'West', NULL, 'East', the result is 2

This is consistent with the general rule: aggregates ignore NULLs. If you need to count NULL as a distinct value, use a workaround:

SELECT COUNT(DISTINCT COALESCE(region, 'Unknown')) FROM orders;

NULL in GROUP BY

GROUP BY treats NULL as a distinct value. All rows with NULL in the group key are grouped together:

SELECT region, COUNT(*) AS order_count
FROM orders
GROUP BY region;
-- Returns one row for NULL region (if any exist)

This is usually the desired behavior — you want to see how many orders have no region assigned. The NULL group appears in the result alongside the named groups.

The NULL Safety Checklist

Before running an analytical query, check for NULL risks:

1. Filters: Does any WHERE condition use =, !=, <, >, IN, or NOT IN on a column that might contain NULL? If so, decide whether NULL rows should be included or excluded, and write the condition accordingly.

2. Computed fields: Does any arithmetic expression involve a column that might contain NULL? If so, wrap it in COALESCE with a business-meaningful default.

3. Aggregations: Does the report use COUNT(column) when it should use COUNT(*), or vice versa? Does SUM or AVG need a COALESCE wrapper for the all-NULL case?

4. Joins: Does the join key contain NULL? If so, inner joins will silently drop those rows. Use an outer join if you need to preserve them.

5. Sorting: Does the sort column contain NULL? If so, specify NULLS LAST or NULLS FIRST explicitly.

Pattern: NULL-Safe Filter

-- Instead of this (drops NULL rows silently):
WHERE status != 'cancelled'

-- Use this (explicit about NULL handling):
WHERE status IS NULL OR status != 'cancelled'

-- Or this (treat NULL as a default):
WHERE COALESCE(status, 'unknown') != 'cancelled'

Pattern: NULL-Safe Computation

-- Instead of this (NULL if any operand is NULL):
SELECT quantity * unit_price * (1 - discount_pct / 100) AS net_revenue

-- Use this (NULL-safe):
SELECT quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100) AS net_revenue

Pattern: NULL-Safe Aggregation

-- Instead of this (NULL if all values are NULL):
SELECT AVG(rating) FROM reviews

-- Use this (0 if all values are NULL):
SELECT COALESCE(AVG(rating), 0) AS avg_rating FROM reviews

Pattern: NULL-Safe Division

-- Instead of this (error if quantity is 0):
SELECT total_amount / quantity AS avg_price

-- Use this (NULL if quantity is 0):
SELECT total_amount / NULLIF(quantity, 0) AS avg_price

-- Use this (0 if quantity is 0):
SELECT COALESCE(total_amount / NULLIF(quantity, 0), 0) AS avg_price

Data Quality Queries

NULL handling is not just about writing correct queries — it is about understanding your data. These queries profile NULL distribution:

-- Count NULLs per column
SELECT
    COUNT(*) AS total_rows,
    COUNT(customer_id) AS known_customers,
    COUNT(*) - COUNT(customer_id) AS null_customers,
    COUNT(ship_date) AS shipped_orders,
    COUNT(*) - COUNT(ship_date) AS unshipped_orders
FROM orders;

-- Percentage of NULLs per column
SELECT
    ROUND(100.0 * (COUNT(*) - COUNT(customer_id)) / COUNT(*), 2) AS pct_null_customer,
    ROUND(100.0 * (COUNT(*) - COUNT(ship_date)) / COUNT(*), 2) AS pct_null_ship_date
FROM orders;

Run these before building reports. Knowing where the NULLs are prevents surprises when the numbers do not add up.

What You Should Remember

NULL is not a value — it is a marker for missing information. Three-valued logic means NULL comparisons return UNKNOWN, and WHERE keeps only TRUE rows. Use IS NULL and IS NOT NULL to test for NULL. Use COALESCE to replace NULL with a default. Use NULLIF to convert sentinel values to NULL. And always check whether your filters, computations, and aggregations handle NULL the way the business question requires.