Ian Klosowicz
NULL is not zero. It's not an empty string. It's the absence of a value, and SQL treats it differently from everything else in ways that break queries in ways that don't throw errors -- they just return wrong answers. Understanding how NULL behaves, and how to handle it deliberately, is one of the highest-leverage SQL skills for analysts.
This guide covers what NULL actually is, how it propagates through comparisons and arithmetic, and the functions you use to handle it cleanly in real queries.
NULL also quietly changes your results once you start grouping, which is one of the sharper edges in how aggregations handle NULL.
NULL represents missing or unknown information. It's a marker that says "we don't know what this value is" -- not that the value is zero, blank, or absent in any meaningful sense. It's unknown.
This distinction matters because SQL has to handle it consistently. If you don't know a value, you can't truthfully say it equals anything, including another unknown. You can't add it to a number and get a meaningful result. You can't sort it reliably next to known values.
NULL shows up in real data constantly:
The earlier you catch and handle NULLs in a query, the less likely they are to propagate silently into your final results.
Standard Boolean logic has 2 values: TRUE and FALSE. SQL has 3: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL returns UNKNOWN, not TRUE or FALSE. And WHERE clauses only return rows where the condition evaluates to TRUE.
This is the root cause of the most common NULL-related bugs.
-- These all return UNKNOWN, not TRUE or FALSE
NULL = NULL -- UNKNOWN
NULL = 1 -- UNKNOWN
NULL != 1 -- UNKNOWN
NULL > 0 -- UNKNOWN
NULL = 'active' -- UNKNOWN
The practical consequence: if you try to filter for NULLs using =, you get no rows back -- not because there are no NULLs, but because the comparison never evaluates to TRUE.
-- Wrong: returns no rows even when NULLs exist
SELECT * FROM users WHERE phone_number = NULL;
-- Correct: IS NULL is the only reliable way to test for NULL
SELECT * FROM users WHERE phone_number IS NULL;
I've seen this exact bug in production queries more times than I'd like to admit. Someone writes WHERE status = NULL expecting to find unassigned records, gets zero rows back, assumes there are no unassigned records, and makes a decision based on incomplete data. The query didn't error. It just returned the wrong answer silently.
IS NULL and IS NOT NULL are the correct operators for testing NULL. They evaluate to TRUE or FALSE, never UNKNOWN, which is what makes them work where = and != don't.
-- Find rows with a missing phone number
SELECT user_id, email
FROM users
WHERE phone_number IS NULL;
-- Find rows where phone number is present
SELECT user_id, email, phone_number
FROM users
WHERE phone_number IS NOT NULL;
-- Combine: find users who have email but no phone
SELECT user_id, email
FROM users
WHERE email IS NOT NULL
AND phone_number IS NULL;
In WHERE clauses, IS NULL and IS NOT NULL are how you segment data by completeness. This is especially common when auditing data quality: how many rows are missing values in each column, which records are incomplete, which ones are ready to use.
-- Quick data quality count across multiple columns
SELECT
COUNT(*) AS total_rows,
COUNT(email) AS has_email,
COUNT(phone_number) AS has_phone,
COUNT(company_name) AS has_company,
SUM(CASE WHEN email IS NULL AND phone_number IS NULL THEN 1 ELSE 0 END) AS no_contact_info
FROM contacts;
Note: COUNT(column_name) counts only non-NULL values. COUNT(*) counts all rows. This is one of the key behavioral differences to keep in mind when auditing completeness.
COALESCE takes a list of expressions and returns the first one that isn't NULL. It's the standard way to substitute a default value for NULL, or to fall back from one column to another when the first is missing.
-- Replace NULL with a default value
SELECT
user_id,
COALESCE(preferred_name, first_name, 'Unknown') AS display_name
FROM users;
-- Replace NULL revenue with 0 for arithmetic
SELECT
order_id,
COALESCE(discount_amount, 0) AS discount,
order_total - COALESCE(discount_amount, 0) AS net_total
FROM orders;
-- Fall through a chain: use the first non-NULL address field
SELECT
customer_id,
COALESCE(billing_address, shipping_address, 'No address on file') AS contact_address
FROM customers;
COALESCE evaluates its arguments in order and stops at the first non-NULL. It works with any data type, as long as all arguments are compatible types. Mixing a text column and an integer in the same COALESCE call causes a type error in most databases.
A pattern I use in almost every pipeline that touches financial data: COALESCE every nullable numeric column before doing arithmetic. A single NULL anywhere in an arithmetic expression returns NULL for the entire expression, which then flows downstream into aggregations and makes totals wrong without any error.
-- Safe arithmetic even with nullable columns
SELECT
order_id,
COALESCE(unit_price, 0) * COALESCE(quantity, 0) AS line_total,
COALESCE(tax_amount, 0) + COALESCE(shipping_amount, 0) AS fees
FROM order_line_items;
NULLIF is the inverse of COALESCE. It takes 2 arguments and returns NULL if they're equal, otherwise returns the first argument. The most common use is treating a sentinel value (like 0 or an empty string) as NULL so that aggregation functions exclude it correctly.
-- Treat 0 as NULL (so it's excluded from AVG)
SELECT AVG(NULLIF(response_time_ms, 0)) AS avg_response_time
FROM api_logs;
-- Treat empty string as NULL
SELECT
user_id,
NULLIF(TRIM(notes), '') AS notes_cleaned
FROM support_tickets;
-- Safe division: avoid divide-by-zero by turning 0 denominator into NULL
SELECT
campaign_id,
clicks / NULLIF(impressions, 0) AS click_through_rate
FROM ad_campaigns;
That last pattern -- NULLIF for safe division -- is one of the most useful SQL patterns in analytics. Dividing by zero throws an error in most databases. Dividing by NULL returns NULL instead of erroring, which is usually the right behavior: if there were no impressions, the CTR is unknown, not zero. NULLIF(denominator, 0) handles this cleanly in one function.
Aggregate functions -- COUNT, SUM, AVG, MIN, MAX -- all ignore NULL values by default, with one exception: COUNT(*).
-- COUNT behavior with NULLs
SELECT
COUNT(*) AS total_rows, -- includes rows where discount IS NULL
COUNT(discount) AS rows_with_discount, -- only rows where discount is not NULL
SUM(discount) AS total_discounts, -- sums only non-NULL discounts
AVG(discount) AS avg_discount -- average of non-NULL discounts only
FROM orders;
The AVG behavior deserves attention. If 100 orders have a discount_amount column and only 20 have a non-NULL value, AVG(discount_amount) averages only those 20. If you want the average across all 100 orders (treating no discount as $0), you need COALESCE first:
-- Average over all orders, treating no-discount as $0
SELECT AVG(COALESCE(discount_amount, 0)) AS avg_discount_all_orders
FROM orders;
-- Average over only orders that had a discount
SELECT AVG(discount_amount) AS avg_discount_when_discounted
FROM orders
WHERE discount_amount IS NOT NULL;
Both are valid, but they answer different questions. Knowing which one you need is the analysis part. SQL will happily compute either without telling you there was a choice.
NULLs in JOIN keys cause rows to be dropped silently. Because NULL != NULL evaluates to UNKNOWN, a row where the join key is NULL on either side will never match any row on the other side -- including rows where the other side's key is also NULL.
-- If user_id is NULL in either table, the JOIN drops that row
SELECT o.order_id, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;
-- Orders where user_id IS NULL are excluded silently
This is usually the correct behavior -- you don't want to match unknown users to each other. But it means you need to know your data: if nullable join keys exist in your tables, an INNER JOIN will quietly drop those rows. If that's not what you want, use a LEFT JOIN and handle the NULLs explicitly in the result.
-- LEFT JOIN preserves all orders, even those without a matching user
SELECT
o.order_id,
o.order_date,
COALESCE(u.email, 'no user on file') AS customer_email
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;
Outer join results are a major source of NULLs in analytical queries. When you do a LEFT JOIN and there's no match on the right side, every column from the right table is NULL for that row. Always check for this when an aggregate result looks lower than expected -- dropped rows from NULL join keys are a common culprit.
NULLs cause the most trouble inside joins, so it helps to understand how NULLs behave in joins specifically.
Any arithmetic expression involving NULL returns NULL. This propagates silently through calculations and is one of the most common sources of unexpectedly NULL columns in query results.
NULL + 1 -- NULL
NULL * 100 -- NULL
NULL / 2 -- NULL
100 - NULL -- NULL
CONCAT('hello', NULL) -- NULL in some databases; 'hello' in others (CONCAT vs ||)
The fix is COALESCE before the arithmetic:
-- Safe: replace NULL inputs before calculation
SELECT
order_id,
COALESCE(subtotal, 0) + COALESCE(tax, 0) + COALESCE(shipping, 0) AS total_due
FROM orders;
String concatenation with NULL varies by database. PostgreSQL's || operator returns NULL if any part is NULL. CONCAT() in MySQL and SQL Server treats NULL as an empty string. This inconsistency is worth knowing when moving queries between databases.
CASE gives you fine-grained control over NULL behavior that COALESCE and NULLIF can't always provide. It's the right tool when the NULL handling logic isn't just "substitute a default" -- when you need to branch based on whether a value is NULL.
-- Categorize rows by NULL status
SELECT
order_id,
CASE
WHEN discount_amount IS NULL THEN 'no discount'
WHEN discount_amount = 0 THEN 'zero discount'
WHEN discount_amount > 0 THEN 'discounted'
END AS discount_status
FROM orders;
-- Label data quality
SELECT
contact_id,
CASE
WHEN email IS NULL AND phone IS NULL THEN 'no contact info'
WHEN email IS NULL THEN 'phone only'
WHEN phone IS NULL THEN 'email only'
ELSE 'complete'
END AS contact_completeness
FROM contacts;
One important detail: CASE WHEN col = NULL THEN ... will never match. Inside a CASE expression, NULL comparisons still follow three-valued logic. Always use IS NULL and IS NOT NULL inside CASE conditions, not = NULL.
NULL and an empty string ('') are different values in SQL. NULL means the value is unknown or absent. An empty string is a known value -- it just happens to have no characters. Most databases treat them as completely distinct.
-- These are NOT equivalent
WHERE notes IS NULL -- rows with no value
WHERE notes = '' -- rows with an empty string value
WHERE notes IS NULL OR notes = '' -- rows with either
In real data, both show up. Users leave fields blank (which some applications store as an empty string, others as NULL, depending on how the app was built). Pipelines that don't normalize this create a situation where your query misses half the "missing" data depending on which condition you write.
The NULLIF(TRIM(col), '') pattern from the string functions guide handles this by converting empty strings to NULL, so you can use IS NULL consistently:
-- Normalize: treat empty strings as NULLs
WITH normalized AS (
SELECT
contact_id,
NULLIF(TRIM(email), '') AS email,
NULLIF(TRIM(phone), '') AS phone,
NULLIF(TRIM(notes), '') AS notes
FROM raw_contacts
)
SELECT
contact_id,
COALESCE(email, 'not provided') AS email,
CASE
WHEN email IS NULL AND phone IS NULL THEN 'unreachable'
ELSE 'reachable'
END AS status
FROM normalized;
This CTE pattern -- normalize first, then query -- is the cleanest way to handle mixed NULL / empty string data without scattering NULLIF calls throughout every downstream query.
If you want to see this kind of data cleaning pattern applied to real portfolio projects, Analyst Hive walks through it as part of the Month 1 curriculum.
Why does WHERE column = NULL return no rows?
Because NULL = NULL evaluates to UNKNOWN, not TRUE. SQL's WHERE clause only returns rows where the condition is TRUE. Any comparison to NULL using = or != returns UNKNOWN, which is filtered out. Use IS NULL instead of = NULL to correctly identify rows where a column has no value.
Does COUNT(*) include NULL values?
Yes. COUNT(*) counts every row regardless of what's in any column. COUNT(column_name) counts only rows where that specific column is not NULL. This is a fundamental difference that changes totals significantly when a column has many NULLs. Always be explicit about which behavior you want.
What's the difference between COALESCE and ISNULL / NVL?
COALESCE is ANSI SQL and works across all major databases -- it takes any number of arguments and returns the first non-NULL. ISNULL is SQL Server-specific and takes exactly 2 arguments. NVL is Oracle-specific and also takes 2 arguments. COALESCE is the portable choice unless you're writing SQL that will only ever run on one specific database.
Why does AVG ignore NULLs but that sometimes gives the wrong answer?
AVG ignores NULLs by design, which means it averages only the rows with actual values. If you have 100 rows and 40 are NULL, AVG computes the average of 60 rows, not 100. Whether that's right depends on what you're measuring. If NULL means "this didn't apply" and you want the average only among cases where it did apply, the default behavior is correct. If NULL means "we don't have this data yet" and you want the average across all cases, use COALESCE to substitute a default before averaging.
How do I sort NULLs to the bottom in ORDER BY?
Most databases put NULLs at the end when sorting ASC and at the beginning when sorting DESC. PostgreSQL and some others let you control this explicitly: ORDER BY column ASC NULLS LAST or ORDER BY column DESC NULLS FIRST. In SQL Server, use CASE to assign a sort value: ORDER BY CASE WHEN column IS NULL THEN 1 ELSE 0 END, column ASC.
Can a primary key column be NULL?
No. Primary key columns must be NOT NULL by definition in standard SQL. A primary key uniquely identifies each row, and NULL means unknown -- you can't uniquely identify something with an unknown value. Most databases enforce this at the schema level and will reject a primary key definition that allows NULLs.
NULL is one of the most consistent sources of silent query bugs in SQL. The rules are simple once you know them: use IS NULL instead of = NULL, use COALESCE to substitute defaults before arithmetic, use NULLIF to convert sentinel values to NULL before aggregating, and always check what your JOIN is doing with nullable keys.
Most of these mistakes don't produce errors. They produce wrong answers that look right until someone checks them. Building the NULL-handling habits early is much easier than debugging a pipeline that's been returning subtly incorrect numbers for months.
If you're building SQL skills as part of a data analytics career transition, join Analyst Hive. The program covers the SQL patterns that show up in real analyst work and applies them in portfolio projects that go on your resume.
