SQL Interview Deep Dive
NULL handling appears as a standalone topic in 1.7% of SQL questions. But NULLs cause silent bugs in JOINs, aggregations, and CASE expressions. Interviewers who test NULLs are testing whether you actually debug your own queries.
NULL bugs are silent. Your query runs, returns results, and the results are wrong. Practice until NULL handling is instinct.
Each of these has at least one non-obvious behavior that interviewers test. Knowing the syntax is not enough. You need to know the edge cases.
WHERE column IS NULL -- never use = NULLThe only correct way to check for NULL. NULL = NULL evaluates to NULL (not TRUE), so WHERE column = NULL returns zero rows. This is the most common NULL mistake in interviews.
Interview tip: If you write WHERE x = NULL in an interview, it is an immediate red flag. Always use IS NULL. Some candidates also forget that WHERE x != NULL also returns nothing.
COALESCE(preferred_name, first_name, 'Unknown')Returns the first non-NULL argument. Takes any number of arguments. This is the standard way to provide fallback values and handle missing data in calculations.
Interview tip: COALESCE with two arguments replaces IFNULL/ISNULL/NVL. Use COALESCE because it is ANSI SQL and works in every dialect. Interviewers notice when you use the portable version.
NULLIF(column, 0) -- returns NULL if column = 0Returns NULL if the two arguments are equal, otherwise returns the first argument. The primary use case is preventing division by zero: revenue / NULLIF(impressions, 0).
Interview tip: NULLIF shows up in interview questions involving ratios and percentages. If you see a division in the problem, think about whether the denominator could be zero.
NULL AND TRUE = NULL | NULL OR TRUE = TRUESQL uses three-valued logic: TRUE, FALSE, and NULL (unknown). Any comparison with NULL returns NULL, not FALSE. This means NOT (NULL) is also NULL. WHERE clauses only include rows where the condition is TRUE.
Interview tip: The classic trap: WHERE id NOT IN (SELECT id FROM other_table) returns nothing if other_table has any NULL ids. Use NOT EXISTS instead. This question has ended many interviews early.
-- NULL != NULL, so NULLs never match in JOIN conditionsNULL values never satisfy a JOIN equality condition because NULL = NULL is NULL (not TRUE). This means rows with NULL join keys are silently dropped in INNER JOINs and appear as non-matches in OUTER JOINs.
Interview tip: When your JOIN produces fewer rows than expected, check for NULLs in the join columns first. This is the most common cause of 'my query is wrong but I do not know why' in interviews.
-- COUNT(*) counts all rows. COUNT(column) skips NULLs.Aggregate functions (SUM, AVG, MIN, MAX) ignore NULL values. COUNT(*) counts rows. COUNT(column) counts non-NULL values. This distinction changes results when your data has gaps.
Interview tip: If an interviewer asks 'what is the average score?' and some scores are NULL, clarify: should NULLs be treated as zero or excluded? AVG ignores NULLs by default, which inflates the average. Use COALESCE(score, 0) if zeros are intended.
NULL bugs do not throw errors. They silently change your results. This makes them the hardest category of SQL bug to catch under interview pressure.
Trap 1: NOT IN with NULLs. WHERE id NOT IN (1, 2, NULL) returns zero rows for every possible value of id. The NULL in the list makes the entire NOT IN evaluate to NULL (unknown), and WHERE NULL filters out the row. This is the single most common NULL trap in interviews. Use NOT EXISTS instead.
Trap 2: Aggregating nullable columns. AVG(score) ignores NULLs entirely. If 8 out of 10 users have a score of 100 and 2 have NULL, the average is 100, not 80. Whether this is correct depends on the business question. Interviewers expect you to ask.
Trap 3: NULL in string concatenation. 'Hello' || NULL returns NULL in standard SQL. One NULL column wipes out the entire concatenated result. Use CONCAT() (which treats NULLs as empty strings) or wrap each column in COALESCE.
Trap 4: COUNT(*) vs COUNT(column). COUNT(*) counts rows. COUNT(email) counts non-NULL emails. If you want the percentage of users with an email, it is COUNT(email) * 1.0 / COUNT(*). Getting this backwards is a silent bug.
Five problems that test NULL handling in realistic scenarios. Each one contains at least one NULL trap.
Table employees has columns (id, manager_id). Some employees have manager_id = NULL (they are top-level). Write a query to find all employees who are NOT managers. Explain why NOT IN fails here and provide a correct solution.
SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM employees) returns nothing because the subquery contains NULLs. Use NOT EXISTS or add WHERE manager_id IS NOT NULL to the subquery.
A survey_responses table has (user_id, question_id, score). Not all users answered all questions (missing rows, not NULL scores). Calculate the average score per question, treating non-respondents as 0. Compare this to the naive AVG.
CROSS JOIN users with questions to get all possible (user, question) pairs. LEFT JOIN to survey_responses. Use COALESCE(score, 0) in the AVG. The naive AVG on survey_responses alone ignores non-respondents entirely.
An ad_campaigns table has (campaign_id, impressions, clicks, spend). Calculate click-through rate (clicks/impressions) and cost per click (spend/clicks). Handle campaigns with zero impressions or zero clicks without errors.
Use clicks * 1.0 / NULLIF(impressions, 0) for CTR and spend / NULLIF(clicks, 0) for CPC. The NULLIF converts zero to NULL, and dividing by NULL returns NULL instead of an error.
Given users (user_id, signup_date) and orders (order_id, user_id, order_date), find users who signed up in January 2024 but never placed an order. Write it three ways: LEFT JOIN + IS NULL, NOT EXISTS, and NOT IN. Explain which is safest.
LEFT JOIN + IS NULL: join on user_id, filter WHERE orders.order_id IS NULL. NOT EXISTS: correlated subquery. NOT IN: only safe if orders.user_id has no NULLs. NOT EXISTS is safest because it handles NULLs correctly in all cases.
A contacts table has (id, email, phone, name). Find duplicate pairs where email matches OR phone matches, but either field could be NULL. Two NULLs should NOT count as a match. Return all duplicate pairs with their match reason.
Use a self-join with conditions like: (a.email = b.email AND a.email IS NOT NULL) OR (a.phone = b.phone AND a.phone IS NOT NULL). The IS NOT NULL clause prevents two NULLs from matching. Add a.id < b.id to avoid duplicate pairs.
Given a users table and an orders table, find all users who signed up in Q1 2024 but have zero orders. Show their name, signup date, and a column indicating order count (0 for everyone in this result).
SELECT
u.name,
u.signup_date,
COALESCE(COUNT(o.order_id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE u.signup_date >= '2024-01-01'
AND u.signup_date < '2024-04-01'
GROUP BY u.user_id, u.name, u.signup_date
HAVING COUNT(o.order_id) = 0
ORDER BY u.signup_date;LEFT JOIN preserves every user even when there is no matching order. COUNT(o.order_id) returns 0 (not NULL) when all joined values are NULL, because COUNT of a column skips NULLs and returns 0 for an empty set. The HAVING clause filters to only users with zero orders. An alternative approach is LEFT JOIN + WHERE o.order_id IS NULL, which avoids the GROUP BY entirely.
Expected output
name | signup_date | order_count
-------------+-------------+------------
Kim Nguyen | 2024-01-05 | 0
Lars Berg | 2024-02-12 | 0
Priya Rao | 2024-03-28 | 0NULL bugs are silent and invisible. The only way to catch them reliably is to have practiced every trap before you see it in an interview.