Working with NULL Values

NULL in SQL represents the complete absence of a value — it means the information is unknown, missing, or not applicable for that row. It is fundamentally different from zero, empty string, or false. Zero is a known numeric value. Empty string is a known string of zero length. False is a known boolean value. NULL is the absence of any known value.

The distinction matters enormously in queries. WHERE salary = 0 finds employees with a salary explicitly set to zero — perhaps interns or volunteers. WHERE salary IS NULL finds employees whose salary has not been recorded — perhaps newly added rows or data entry errors. These are completely different sets of rows. Similarly, WHERE name = '' finds rows where name is an empty string — someone entered nothing but submitted the form. WHERE name IS NULL finds rows where the name field was never touched at all.

In FreshCart's orders table, delivery_date IS NULL means the delivery has not happened yet — not that the delivery date is unknown, but that it does not exist because the event has not occurred. This is the "not applicable" use of NULL, distinct from the "unknown" use. Both are represented identically in SQL, which is why understanding what NULL means in context is important before writing queries that filter on nullable columns.

Three-valued logic extends standard two-valued (TRUE/FALSE) boolean logic by adding a third value: NULL (unknown). SQL uses three-valued logic because relational databases store real-world information that is sometimes incomplete or unknown. Forcing every value into TRUE or FALSE when the information is genuinely unknown would produce incorrect results — treating "unknown" as either "yes" or "no" introduces false precision.

The rules of three-valued logic follow from the meaning of NULL as "unknown." Any comparison with NULL returns NULL, because comparing a known value to an unknown value cannot produce a definitive TRUE or FALSE answer. TRUE AND NULL = NULL (true combined with unknown is still uncertain). FALSE AND NULL = FALSE (false combined with anything is false). TRUE OR NULL = TRUE (true combined with anything is true). FALSE OR NULL = NULL (false combined with unknown is uncertain). NOT NULL = NULL (reversing unknown is still unknown).

The practical consequence: the WHERE clause discards rows where the condition evaluates to NULL — only rows where the condition is definitely TRUE are included. This is the correct behaviour logically, but it means developers must be explicit about NULL handling. A condition intended to match "anything that is not Seattle" written as WHERE city <> 'Seattle' silently excludes rows where city is NULL, because NULL <> 'Seattle' evaluates to NULL, not TRUE. The correct version that includes NULL cities is WHERE city <> 'Seattle' OR city IS NULL.

COALESCE(value1, value2, ..., valueN) evaluates its arguments left to right and returns the first non-NULL value. If all arguments are NULL, it returns NULL. It is the standard SQL function for providing default values when a column might be NULL and for implementing fallback logic when multiple columns might supply a value.

Real-world example 1 — default values in calculations: when calculating the discounted price of a product, if discount_pct is NULL (no discount applies), multiplying by NULL produces NULL for the entire calculation. COALESCE(discount_pct, 0) replaces NULL with 0 so the calculation works correctly: unit_price * (1 - COALESCE(discount_pct, 0) / 100). The result is the original unit_price when no discount applies, rather than NULL.

Real-world example 2 — contact information fallback: a CRM might have preferred_email, work_email, and personal_email as separate columns. To get the best available contact: COALESCE(preferred_email, work_email, personal_email, 'No email on file'). This returns the preferred email if set, falls back to work email, then personal email, and finally a literal string if none are available. This fallback chain pattern is extremely common in data integration and reporting where data comes from multiple sources with varying completeness.

NOT IN with a subquery that contains NULL values causes the entire WHERE clause to return NULL for every row — resulting in zero rows in the output. This happens silently with no error message, making it one of the most dangerous NULL traps in SQL.

The mechanism: NOT IN (val1, val2, NULL) is internally equivalent to NOT (col = val1 OR col = val2 OR col = NULL). For any row in the outer query, col = NULL always evaluates to NULL (from three-valued logic). When the final OR combines a NULL result with the other conditions, and those conditions are also NULL or FALSE, the OR produces NULL. NOT NULL = NULL. The WHERE clause sees NULL and discards the row. This happens for every single row in the outer table — returning zero results.

The safe alternatives are: NOT EXISTS (SELECT 1 FROM subquery WHERE subquery.col = outer.col) — this correctly handles NULL because EXISTS checks for the presence of rows, not value equality. Or, filter NULLs from the subquery explicitly: NOT IN (SELECT col FROM table WHERE col IS NOT NULL). In practice, NOT EXISTS is almost always preferable to NOT IN for correlated lookups because it handles NULL correctly, often performs better on large datasets, and its intent is clearer. This is a rule worth memorising: when you mean "no matching row exists," write NOT EXISTS, not NOT IN.

NULLIF(value1, value2) returns NULL if value1 equals value2, otherwise returns value1. It is the inverse of COALESCE — where COALESCE replaces NULL with a value, NULLIF converts a specific value into NULL. The most common use case is preventing division-by-zero errors by converting a zero denominator into NULL before division.

Division by zero example: calculating profit margin as (unit_price - cost_price) / unit_price would error if unit_price is zero. With NULLIF: (unit_price - cost_price) / NULLIF(unit_price, 0). If unit_price is zero, NULLIF returns NULL, and dividing by NULL returns NULL instead of raising an error. The result row shows NULL for the margin, which correctly communicates "this calculation is not applicable" rather than crashing the query.

Second use case — treating sentinel values as NULL: some legacy systems use 0 or empty string as a "no value" indicator instead of NULL. COALESCE(NULLIF(phone, ''), 'No phone') handles both cases — if phone is '' (empty string), NULLIF converts it to NULL, then COALESCE replaces NULL with 'No phone'. If phone is already NULL, NULLIF leaves it NULL and COALESCE replaces it. If phone is '+91-9876543210', NULLIF leaves it as-is and COALESCE returns it. The combination of NULLIF and COALESCE is a standard pattern for normalising data from sources that mix NULL and empty string to represent "no value."

Cause: You used = to compare a column to NULL. NULL = NULL evaluates to NULL (not TRUE), and the WHERE clause discards all NULL-result rows. This is the single most common NULL mistake. The query runs without error and returns zero rows — silently incorrect behaviour that can be very hard to debug if you do not know three-valued logic.

Fix: Replace = NULL with IS NULL: WHERE delivery_date IS NULL. Replace <> NULL with IS NOT NULL: WHERE delivery_date IS NOT NULL. These are the only correct operators for NULL checks. Most SQL editors have a linting feature that flags = NULL and <> NULL as potential bugs — enable it in your editor settings.

Cause: The NOT IN subquery returned at least one NULL value. NOT IN (val1, NULL) internally includes a col = NULL comparison which always evaluates to NULL. Since NOT NULL = NULL, every row in the outer query evaluates to NULL and is discarded by WHERE. This happens even if only one row in the subquery has a NULL value — one NULL poisons the entire NOT IN result.

Fix: Replace NOT IN with NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM table WHERE table.col = outer.col). Or add WHERE col IS NOT NULL to the subquery: WHERE col NOT IN (SELECT col FROM table WHERE col IS NOT NULL). To diagnose: run the subquery alone and check for NULLs: SELECT COUNT(*) FROM subquery_table WHERE col IS NULL. If this returns any rows, NOT IN will fail.

Cause: AVG() ignores NULL values — it only averages the non-NULL rows. If you have 30 orders but 12 have NULL delivery dates (undelivered), AVG(delivery_date - order_date) averages only the 18 delivered orders. The result is the average delivery time for delivered orders, not for all orders. If you expected the average across all orders with NULL counted as some value (like 0 or today's date), the result will be higher than expected.

Fix: Make the NULL handling explicit: AVG(COALESCE(delivery_date - order_date, 0)) — this replaces NULL with 0 days before averaging. Or use a CASE expression: AVG(CASE WHEN delivery_date IS NOT NULL THEN delivery_date - order_date END) — which produces the same result as plain AVG but makes the NULL exclusion explicit and self-documenting. Always document in comments whether your average intentionally excludes NULLs or whether COALESCE should be used.

Cause: One of the columns in your calculation contains NULL for that row, and NULL propagates through arithmetic. unit_price * NULL = NULL, NULL + 100 = NULL. Any calculation that touches a NULL column produces NULL for that row's result. This commonly happens with optional columns like discount_pct or bonus_amount that are NULL when not applicable.

Fix: Wrap nullable columns in COALESCE with an appropriate default before using them in calculations: unit_price * (1 - COALESCE(discount_pct, 0) / 100). Salary + COALESCE(bonus, 0). The default value should be whatever makes mathematical sense — 0 for additive values, 1 for multiplicative factors, the column itself for no-op operations. Check which column is NULL by running SELECT * FROM table WHERE the_nullable_column IS NULL LIMIT 5 to confirm the source of the NULL.

Cause: The email column allows NULL, and NULL values are exempt from UNIQUE constraint enforcement because NULL IS NOT DISTINCT FROM NULL is false in standard SQL comparison — each NULL is considered distinct from every other NULL. A UNIQUE constraint on email prevents duplicate non-null emails but allows any number of NULL emails. If multiple users are inserted without an email address, all of them get NULL and all pass the UNIQUE check.

Fix: If NULL emails should not be allowed, add NOT NULL to the email column: ALTER TABLE customers ALTER COLUMN email SET NOT NULL. If NULL emails are valid but only one NULL should be allowed, this cannot be enforced with standard UNIQUE — it requires a filtered unique index. In PostgreSQL: CREATE UNIQUE INDEX idx_customers_email ON customers (email) WHERE email IS NOT NULL — this enforces uniqueness on non-null emails while allowing multiple NULLs. In MySQL, the standard UNIQUE already allows multiple NULLs and there is no filtered index syntax for this specific case.

The FreshCart operations team wants a delivery performance report. Write a query that shows all orders with: order_id, order_date, a delivery_status column that shows 'Delivered' if delivery_date IS NOT NULL or 'Pending' if delivery_date IS NULL, the actual delivery_date (show 'Not yet delivered' if NULL), and days_taken (the number of days between order_date and delivery_date — show NULL for pending orders). Sort by order_date descending.