8 SQL Interview Answers That Sound Right and Are Wrong

Q: What SQL mistakes do freshers make most often in interviews?

NULL handling comes first: comparing with `= NULL`, forgetting that `<>` drops NULL rows, and using NOT IN against a list that contains NULL. After that come filters placed in the wrong clause (WHERE instead of HAVING, or WHERE after a LEFT JOIN), COUNT(*) against COUNT(column), BETWEEN on timestamps and integer division.

Q: Why does NOT IN return no rows when the list contains a NULL?

Because `x NOT IN (1, NULL)` expands to `x <> 1 AND x <> NULL`. The second part is unknown for every x, so the whole condition can never be true. Use NOT EXISTS, or remove the NULLs from the subquery.

By Pooja Goenka · 2026-10-07

Short answer: the SQL answers that cost freshers offers are rarely syntax errors. They are confident answers that quietly return the wrong rows: NULL comparisons, NOT IN, a filter placed after a LEFT JOIN, COUNT(*) against COUNT(column), BETWEEN on timestamps and integer division. Below are eight of them, each with the answer people give, what the database really does, and what to say instead. I ran every query in SQLite 3.53. Where PostgreSQL or MySQL behave differently, I say so.

Interviewers like these questions because a wrong answer here does not crash anything. It returns a plausible table, and the candidate has to notice that the table is wrong. That is the same skill you need when you read a query written by a colleague, or by an AI tool.

The test data

Every example below uses these three tables. Paste them into any SQLite or PostgreSQL session if you want to follow along.

orders(id, customer_id, amount, status, created_at)
-- 1, 1, 100,  'paid',     '2026-01-10 09:00'
-- 2, 1,  50,  NULL,       '2026-01-31 10:30'
-- 3, 2, 200,  'paid',     '2026-01-15 12:00'
-- 4, 3, NULL, 'refunded', '2026-01-31 00:00'
-- 5, 3,  30,  'paid',     '2026-02-01 08:00'

customers(id, name)        -- 1 Asha, 2 Ravi, 3 Meera, 4 Kabir (no orders)
blocked(customer_id)       -- 1 and NULL

1. "You can filter on SUM() in WHERE"

The confident answer: WHERE SUM(amount) > 100, then group by customer.

The database refuses it. SQLite says "misuse of aggregate: sum()", and PostgreSQL gives a similar error. WHERE runs before rows are grouped, so there is no sum to test yet. HAVING runs after grouping.

Say instead: use GROUP BY customer_id HAVING SUM(amount) > 100. On the test data that returns customer 1 with 150 and customer 2 with 200. Use WHERE for filters on single rows and HAVING for filters on groups.

2. "`status = NULL` finds the missing statuses"

It returns zero rows. In SQL, NULL means unknown, and comparing anything to unknown gives unknown, which WHERE treats as false. status IS NULL returns the one row we expect. Order 2 is the only one with a missing status.

3. "`status <> 'paid'` gives me everything that is not paid"

On the test data it returns one row, the refunded order. Order 2, whose status is NULL, is silently dropped, because NULL <> 'paid' is also unknown. To keep it, write status <> 'paid' OR status IS NULL, which returns both rows. PostgreSQL has a shorter form, status IS DISTINCT FROM 'paid'.

4. "COUNT(*) and COUNT(amount) are the same thing"

SELECT COUNT(*), COUNT(amount) FROM orders returns 5 and 4. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL.

The same trap sits inside averages. AVG(amount) returns 95, because it ignores the NULL. SUM(amount) / COUNT(*) returns 76, because it divides by all five rows. Neither is wrong by itself. The mistake is picking one without deciding whether a missing amount should count as zero. A good answer names that decision out loud.

5. "NOT IN and NOT EXISTS are interchangeable"

This one catches experienced people too. The query SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM blocked) returns nothing at all, because the blocked list contains a NULL. For every customer, "not in (1, NULL)" evaluates to unknown, never to true.

NOT EXISTS (SELECT 1 FROM blocked b WHERE b.customer_id = c.id) returns Ravi, Meera and Kabir, which is what anyone would expect. If the subquery can return NULL, prefer NOT EXISTS, or filter the NULLs out of the list.

6. "A filter in WHERE after a LEFT JOIN keeps all the left rows"

The confident answer: customers LEFT JOIN orders ... WHERE orders.status = 'paid' still lists every customer.

It does not. Kabir has no orders, so his joined status is NULL, and the WHERE condition removes him. The query returns Asha, Ravi and Meera, which is an inner join in disguise. Put the condition in the join instead, LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid', and Kabir comes back with an empty status. The rule is short: a filter on the right-hand table belongs in ON if you want to keep unmatched left rows.

7. "BETWEEN '2026-01-01' AND '2026-01-31' covers all of January"

It stops at the start of 31 January. The order placed at 10:30 on 31 January is missing from the result. The safe pattern for dates and timestamps is a half-open range: created_at >= '2026-01-01' AND created_at < '2026-02-01'. It also works if the column later gains seconds or time zones.

8. "1 / 2 gives 0.5"

In SQLite and PostgreSQL, dividing two integers gives an integer, so SELECT 1/2 returns 0. 1.0 / 2 returns 0.5. MySQL's / returns a decimal, so ask which database the interviewer uses before you answer, or cast one side to a decimal and say why.

How to use these in the room

You do not need to memorise eight rules. Two habits cover most of them. First, whenever a column can be NULL, say what should happen to those rows before the interviewer asks. Second, when you give an answer, say what you would run to check it. A one-line test on three rows of made-up data is a perfectly good answer to "how would you know?".

Practise the habit

Reciting a mistake is easy to forget, and catching one yourself sticks. LogicWiz Interviews has a SQL track for AI mock interviews, by voice or text, and the free plan includes 3 AI sessions a day with a free account. In its spot-the-mistake round the AI gives a confident answer with one planted error, and you find it and say why. The idea is described in Can You Spot the AI's Mistake?. If you are preparing for a wider range of questions, the GenAI interview questions post uses the same format.

Common questions

What SQL mistakes do freshers make most often in interviews?

NULL handling comes first: comparing with = NULL, forgetting that <> drops NULL rows, and using NOT IN against a list that contains NULL. After that come filters placed in the wrong clause (WHERE instead of HAVING, or WHERE after a LEFT JOIN), COUNT(*) against COUNT(column), BETWEEN on timestamps and integer division.

Why does NOT IN return no rows when the list contains a NULL?

Because x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL. The second part is unknown for every x, so the whole condition can never be true. Use NOT EXISTS, or remove the NULLs from the subquery.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, so it cannot use aggregates such as SUM or COUNT. HAVING filters groups after grouping and can use them.

Is COUNT(*) the same as COUNT(column)?

No. COUNT(*) counts every row. COUNT(column) counts only rows where the column is not NULL. On the test data above they return 5 and 4.

How do I check an SQL answer in an interview?

Build three or four rows of data that include a NULL, a duplicate and a boundary value, and run your query on them in your head or on paper. If the interviewer allows a real console, run it. Saying that you would do this is part of a strong answer.