Detailed explanation
NULL in SQL represents missing or unknown information; it is not the same as zero, an empty string, or the text 'NULL'. For example, a NULL phone number may mean that a customer has not provided one. Handling NULL correctly matters because it changes how filtering, calculations, aggregates, and joins behave. Interviewers expect you to distinguish checking whether a value is missing from replacing that missing value for display or computation.
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Ordinary comparisons involving NULL, such as phone = NULL, phone <> NULL, or even NULL = NULL, evaluate to UNKNOWN rather than TRUE or FALSE. A WHERE clause retains only rows whose condition is TRUE, so those comparisons do not identify missing values. Use IS NULL to find them and IS NOT NULL to find present values. Also, NOT UNKNOWN remains UNKNOWN, so simply negating a comparison does not make it include NULL rows.
To supply a fallback, use COALESCE(expression, fallback), which returns the first non-NULL argument and can accept multiple arguments. For example: SELECT name, COALESCE(phone, 'not provided') AS phone FROM customers WHERE deleted_at IS NULL; This selects customers whose deletion timestamp is missing and displays a label when their phone number is missing. It does not update the stored phone value. COALESCE is standard SQL; IFNULL is available in MySQL and SQLite, while NVL is Oracle-specific. Keep arguments type-compatible because type resolution and implicit conversion rules vary by database.
NULL also affects other operations. Arithmetic involving NULL generally produces NULL, while aggregates such as SUM and AVG ignore NULL inputs. COUNT(column) counts non-NULL values, whereas COUNT(*) counts rows. Replacing NULL with zero before AVG changes its denominator and therefore its meaning. Equality joins do not match NULL keys to one another. Be careful with NOT IN when its list or subquery contains NULL: nonmatching comparisons can become UNKNOWN. A correlated NOT EXISTS is often a safer way to express an anti-match, provided you define how missing keys should behave.
Key points to remember
- NULL represents missing or unknown information, not zero or a normal string.
- Use IS NULL and IS NOT NULL; ordinary comparisons with NULL produce UNKNOWN.
- WHERE keeps only TRUE conditions, excluding both FALSE and UNKNOWN.
- COALESCE returns the first non-NULL argument without changing stored data.
- COUNT(*) counts rows; COUNT(column) and most other aggregates ignore NULL inputs.
Common mistakes candidates make
- Writing column = NULL or column <> NULL instead of using IS NULL or IS NOT NULL.
- Replacing missing numeric values with zero without checking whether that changes the business meaning.
- Using NOT IN with nullable subquery results and assuming it behaves like NOT EXISTS.
Likely follow-up questions
How do I find rows where a column is not 5, including NULL values?
Use WHERE column_name <> 5 OR column_name IS NULL. The inequality alone excludes NULL rows because its result for those rows is UNKNOWN.
What is the difference between AVG(amount) and AVG(COALESCE(amount, 0))?
AVG(amount) averages only non-NULL amounts. AVG(COALESCE(amount, 0)) includes missing amounts as zero, so it can produce a different result.
Does COALESCE permanently replace NULL values?
No, using COALESCE in a SELECT only changes the query result. To change stored values, use an UPDATE statement, such as UPDATE customers SET phone = 'not provided' WHERE phone IS NULL, only if storing that label is appropriate.
Interview tip: Explain NULL and three-valued logic first, demonstrate IS NULL and COALESCE with the customer query, then mention one aggregate-related pitfall.