Handling Nulls Correctly for SQL Interviews: The Practical Framework
A revenue dashboard shows ₹0 for a campaign; after one SQL fix, the same cell changes to “data not received yet.” That is the difference between treating NULL as a value and treating it as a signal. In analytics, mishandling NULLs does not just break a query - it quietly changes the business story.
- NULL means missing, unknown, or not applicable - never assume it means zero, blank, or false.
- Use
IS NULLandIS NOT NULL;= NULLand<> NULLdo not work as expected. - SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Rows with UNKNOWN do not pass a normal
WHEREfilter. COUNT(*)counts rows;COUNT(column)counts only non-NULL values.- Aggregates like
SUM,AVG,MIN, andMAXignore NULLs, which can silently bias results. - Use
COALESCEonly after deciding the business meaning of NULL; replacing NULL with 0 too early is dangerous. - For joins and filters, be extra careful with
NOT IN, because a NULL inside the subquery can remove all expected rows.
Big Picture: NULL Handling Is a Business Decision First, SQL Decision Second
The safest way to handle NULLs is not “replace them quickly.” It is: understand what the missingness means, choose the correct SQL behavior, then validate whether the metric still answers the business question.
Core Explanation: The SQL Rules That Actually Matter
NULL is a marker, not a normal value. It tells the database that the value is absent, unknown, or not applicable. That one idea explains most SQL surprises.
1. NULL Does Not Equal Anything - Not Even Another NULL
Because NULL means “unknown,” SQL cannot say whether NULL = NULL is true. The result is UNKNOWN, not TRUE.
2. WHERE Filters Keep TRUE, Not UNKNOWN
A WHERE clause returns rows where the condition is TRUE. Rows where the condition is FALSE or UNKNOWN are excluded. That is why WHERE discount <> 0 also drops rows where discount is NULL.
3. Aggregates Ignore NULLs - Sometimes Correctly, Sometimes Dangerously
Most SQL aggregate functions ignore NULLs. That is often useful, but it can make the denominator smaller than you think.
Worked Example: Why COUNT and AVG Change the Story
Imagine this simplified order table:
Now compare the metrics:
The second average is not automatically “better.” It is correct only if a missing refund amount truly means zero refund. If NULL means “not applicable” or “not processed yet,” forcing it to 0 changes the business meaning.
4. JOINs Create NULLs Too
A LEFT JOIN preserves all rows from the left table. If no matching row exists on the right, SQL fills the right-side columns with NULL. That NULL is not missing input data - it is a non-match created by the join.
5. The NOT IN Trap
NOT IN becomes risky when the subquery contains NULL. If SQL has to evaluate “not equal to an unknown value,” the result can become UNKNOWN and remove rows you expected to keep.
Prefer NOT EXISTS for anti-joins, or explicitly filter NULLs inside the subquery: WHERE key IS NOT NULL.
Definitions You Should Be Able to Say Clearly
- NULL: A database marker showing that a value is missing, unknown, or not applicable - not zero, blank, or false.
- Three-valued logic: SQL logic where a condition can evaluate to TRUE, FALSE, or UNKNOWN.
- COALESCE: A SQL function that returns the first non-NULL expression from a list.
- Imputation: Replacing missing values with estimated or rule-based substitutes for analysis or modelling.
A Practical Framework: Diagnose, Decide, Document
Use this framework whenever you see missing values in a SQL question, dashboard, or analytics case.
How to Measure NULL Quality
When nulls affect a business metric, do not just say “there are many missing values.” Name the measure and the threshold logic.
Case Study: PhonePe and NULLs in Payment Analytics
PhonePe is a useful Indian example because UPI payment data contains different kinds of absence - pending confirmation, failed transaction details, refunds, and non-matching reconciliation records.
In a UPI payment journey, a transaction can be successful, failed, pending, refunded, or reversed. Some fields naturally appear only after a specific event. For example, a bank reference value may be absent while a transaction is pending; a failure reason may be absent for a successful transaction; a refund timestamp may be absent because no refund was initiated.
The risky move would be to replace all missing values with 0 or “NA” and build one dashboard. That would mix very different meanings: not applicable, not yet received, and unexpectedly missing.
The better analytics move is to treat NULLs by business state. Payment-status logic becomes the primary driver of correctness, supported by reconciliation rules, timestamp checks, and separate exception flags for genuinely missing mandatory fields.
Lesson: PhonePe-style payment analytics proves that NULL handling is not a cleaning step at the end. It is part of the business logic that protects success rate, failure analysis, refund ageing, and reconciliation accuracy.
How AI Changes Handling Nulls Correctly
AI does not remove the need to understand NULLs. It makes profiling faster, while making blind code generation more risky.
- AI-assisted data profiling: Tools can scan schemas and sample rows to highlight columns with high NULL rates, sudden date-wise spikes, and suspicious join miss patterns.
- Semantic NULL classification: LLMs can read column names, data dictionaries, and business rules to suggest whether NULL may mean unknown, not applicable, pending, or system error. The analyst still has to verify this with domain owners.
- SQL generation with guardrails: AI can draft queries using
COALESCE,NULLIF,IS NULL, andNOT EXISTS, but it may also incorrectly convert NULLs to 0 unless prompted with the business meaning.
Load the table schema, sample rows, and metric definition into ChatGPT or Claude. Ask: “List every column where NULL could change this metric, classify the likely meaning, and rewrite the SQL with explicit NULL handling assumptions.” Then manually verify the assumptions.
Interview Relevance
“You are calculating average delivery time from an orders table. Some delivered_at values are NULL. How will you handle them?”
A strong answer says: “I will not impute delivery time for undelivered orders. I will calculate TAT for completed deliveries and separately report pending or missing timestamp cases.”
Common Mistake
The mistake: Replacing every NULL with 0 using COALESCE before understanding the business meaning. Why it costs candidates: it changes denominators, biases averages, and hides process exceptions. One-line fix: classify NULL first, then choose whether to keep, filter, flag, or fill it.
What to Revise Next
Once NULL behavior is clear, move to query structure and analytical power. Revise subqueries and CTEs next so your SQL is readable, then window functions so you can handle ranking, running totals, lag, and lead without breaking row-level logic.