NULL Handling

Hook: NULL is SQL's way of saying it is unknown, and interviewers love this question because one wrong = can quietly erase rows from a report.

Question: How do you handle NULL values in SQL when selecting and filtering rows?

Answer: NULL means missing or unknown, not zero or an empty string. To filter it, I use IS NULL or IS NOT NULL, because normal comparisons like = do not work with NULL the way people expect. For display and calculations, I often use COALESCE to replace NULL with a safe default, and I remember that COUNT(column) skips NULLs while COUNT(*) counts rows.

Interview-Ready Answer: I treat NULL as unknown, not as zero or an empty value. In filters, I always use IS NULL or IS NOT NULL because = NULL evaluates to unknown and does not match rows. For output and calculations, I use COALESCE when I want a default, and I remember the big count rule: COUNT(*) counts rows, but COUNT(col) ignores NULLs.

Detailed Explanation:

What NULL really means

In SQL, NULL is not a value like 0 or ''. It means missing, unknown, or not applicable. The key idea is three-valued logic (logic with TRUE, FALSE, and UNKNOWN): comparisons against NULL usually become UNKNOWN, and a WHERE clause keeps only rows where the predicate is TRUE.

1. The database reads a row and evaluates your predicate, such as salary > 50000 or email = NULL.
2. If a NULL appears in a normal comparison, the result is usually UNKNOWN, not TRUE and not FALSE.
3. WHERE only returns rows where the predicate is TRUE. Rows that evaluate to FALSE or UNKNOWN are filtered out.
4. That is why col = NULL matches nothing, while col IS NULL checks the internal null marker directly.
5. COALESCE is a safe display helper: it returns the first non-NULL value, so you can show a default without changing stored data.
6. NULLIF is the opposite-style helper: it turns a chosen value into NULL, which is useful when a sentinel value like 0 should be treated as missing.

When to use each tool

Think in terms of intent: are you testing for missing data, showing a fallback, or counting real values?

Task	Bad choice	Better choice	Why
Test missing	col = NULL	col IS NULL	= gives UNKNOWN
Test present	col <> NULL	col IS NOT NULL	same NULL rule
Show default	n/a	COALESCE(col, 0)	safe output value
Count rows	COUNT(col)	COUNT(*)	skips NULLs

Performance and edge cases

A plain IS NULL predicate is usually sargable (index-friendly; the optimizer can use an index instead of scanning every row). If you wrap the column in a function inside WHERE, such as COALESCE(col, 0) = 0, you may make the predicate harder to index and force a scan.

• With no useful index, filtering is roughly O(n) because every row is checked.
• With a good index, a nullable-column lookup can be closer to O(log n + k), where k is the number of matched rows.
• COUNT(col), SUM(col), and AVG(col) ignore NULL values; if all inputs are NULL, the result can still be NULL.
• NULL sorting differs by database, so if order matters, check whether your dialect supports NULLS FIRST or NULLS LAST.
• One famous dialect quirk: Oracle treats the empty string as NULL in character columns, while many other databases do not.

The mental model to remember is simple: NULL is not a number, not a blank string, and not false; it is unknown. Once you think that way, the correct SQL usually becomes obvious.

Real-World Story: Imagine a checkout service for an online store. Orders have a nullable shipped_at column, and a dashboard uses it to count pending shipments. A developer writes WHERE shipped_at = NULL instead of IS NULL, so the report shows zero pending orders even though the warehouse is backed up.

What goes wrong next is painful: customer support says, “Why are 400 orders late?” while the operations dashboard looks healthy. Logs show no SQL error, because the query is valid SQL; it just evaluates to UNKNOWN and filters everything out. The bug slips through because the result set is empty, not obviously broken, so the team trusts a bad metric for hours.

The fix is not just changing one operator. The real lesson is to treat nullable fields as first-class data, use IS NULL in filters, and be careful with defaults in reporting queries so the business sees reality, not a misleading zero.

Follow-up & Tricky Questions:

• Why does col = NULL return nothing? Because the comparison is not TRUE; it becomes UNKNOWN, and WHERE only keeps TRUE rows.
• What is the difference between COUNT(*) and COUNT(col)? COUNT(*) counts every row, while COUNT(col) ignores NULLs and counts only rows where the column has a real value.
• When should I use COALESCE instead of CASE? Use COALESCE for a simple default, like replacing NULL with 0 or 'N/A'. Use CASE when the logic has more branches than just “first non-NULL”.
• How do NULLs behave in GROUP BY? NULLs are grouped together, so all rows with NULL in the grouped column end up in the same group.
• Can an index help IS NULL filters? Yes, often it can. A simple IS NULL predicate is usually index-friendly, but wrapping the column in a function in the WHERE clause can block index use.
• Does SUM(col) treat NULL as zero? No. Aggregate functions usually ignore NULLs, and if every input is NULL the result can still be NULL.
• Is NULL the same as an empty string? No, not in most databases. Empty string means a real text value with length zero; NULL means unknown or missing. Oracle is a famous exception for empty strings in character columns.
• Does NULL = NULL ever return TRUE? In standard comparisons, no. It is UNKNOWN, which is why you must use NULL-aware syntax or special operators supported by some databases.

Common Mistakes:

• Using = NULL or <> NULL — correction: use IS NULL or IS NOT NULL.
• Confusing NULL with 0 or '' — correction: NULL means unknown/missing, while 0 and empty string are real values.
• Assuming COUNT(col) counts every row — correction: it skips NULLs; use COUNT(*) for total rows.
• Wrapping nullable columns in functions inside WHERE — correction: prefer simple predicates like col IS NULL so the optimizer can use indexes more easily.

Memory Hook: NULL is a question mark, not a value. You do not compare a question mark with =; you ask whether the box is empty with IS NULL.

Cheat Sheet:

• NULL means unknown, missing, or not applicable.
• Use IS NULL / IS NOT NULL for filtering.
• WHERE keeps only TRUE; FALSE and UNKNOWN are dropped.
• COALESCE gives a default display value.
• COUNT(*) counts rows; COUNT(col) skips NULLs.
• Watch dialect quirks like Oracle empty strings and NULL sort order.

Practice Tasks:

• Write a query that finds customers whose phone_number is NULL.
• Add a fallback column with COALESCE(phone_number, 'N/A') in the SELECT list.
• On a sample table, compare COUNT(*), COUNT(phone_number), and COUNT(*) - COUNT(phone_number).