SELECT & filtering
SQL · 6 interview questions
Every SQL query starts by deciding which rows survive and which columns come back. The syntax is the easiest part of the language, which is exactly why interviewers use it to find out whether you understand the parts that aren't syntax.
The one that catches people is NULL. NULL is not a value, it's the absence of one, and comparing against it is neither true nor false — it's unknown. So WHERE referee_id != 2 silently drops every row where referee_id is NULL, because unknown is not true and only true rows survive a WHERE clause. This is the single most common bug in beginner SQL, and it never announces itself; you just get fewer rows than you expected.
The fix is IS NULL and IS NOT NULL, which are the only operators that treat NULL as something you can ask about rather than something you can compare to. Any filter on a nullable column needs to decide, explicitly, what should happen to the NULLs.
DISTINCT deserves the same suspicion. It deduplicates the entire selected row, not the first column, so adding a column to your SELECT can change how many rows DISTINCT returns — which is a surprising amount of debugging for a keyword that looks so simple.
SELECT & filtering interview questions
- Why does
WHERE salary != 100skip rows where salary is NULL? - Because comparing anything to NULL yields unknown, not true or false, and WHERE keeps only rows that evaluate to true. To include them you have to say so:
WHERE salary != 100 OR salary IS NULL. - Why they ask: The most common silent bug in SQL. Interviewers use it to separate people who've written queries from people who've debugged them.
- Why can't you write
WHERE x = NULL? - NULL means 'unknown', so
x = NULLasks whether an unknown equals an unknown — which is itself unknown, never true.IS NULLis a separate operator that tests for absence rather than comparing values. - Why they ask: Tests whether you understand three-valued logic or just memorised a rule.
- What happens to
WHERE id NOT IN (1, 2, NULL)? - It returns no rows at all. NOT IN expands to
id != 1 AND id != 2 AND id != NULL, and that last comparison is unknown for every row, so nothing is ever true. This is whyNOT EXISTSis safer thanNOT INagainst a subquery that might produce NULLs. - Why they ask: A genuinely nasty trap — the query looks right, runs fine, and returns an empty set.
- Does DISTINCT apply to the first column or the whole row?
- The whole selected row.
SELECT DISTINCT a, bdeduplicates unique (a, b) pairs, so adding a column can increase the row count. There's no such thing as DISTINCT on one column while freely selecting others. - Why they ask: People reach for DISTINCT to fix duplicates and are baffled when it doesn't. Usually they wanted GROUP BY.
- In what order does the database actually evaluate a query's clauses?
- Roughly FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Not the order you write them in. That's why you can't use a SELECT alias in a WHERE clause — the alias doesn't exist yet — but you usually can in ORDER BY.
- Why they ask: Explains a whole family of 'unknown column' errors in one sentence.
- Is BETWEEN inclusive of its endpoints?
- Yes, on both ends —
BETWEEN 5 AND 10includes 5 and 10. The trap is with timestamps:BETWEEN '2024-01-01' AND '2024-01-31'excludes almost all of the 31st, because the date literal becomes midnight. For date ranges over timestamps, prefer>= start AND < next_day. - Why they ask: The timestamp half of this shows up in real reporting bugs constantly.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.