Why "What's Wrong With This Query?" Is Its Own Interview Format
A different, very common interview format shows you an actual query — one that runs without error — and asks you to find what's wrong with it. This tests something neither a "write a query from scratch" question nor a pure behavioral question does: your ability to read someone else's SQL critically and spot a bug that isn't a syntax error, just a logic error that quietly produces the wrong answer. This is also exactly the skill code review requires on the job, which is why it's such a popular interviewer format.
This is Part 3 of our situational interview series — see Part 1 and Part 2 for the broader judgment-and-communication scenarios. This one is narrower and more technical: twelve real buggy queries, what's actually wrong with each, and how to explain it clearly when asked.
How to Approach Any "Spot the Bug" Query
Before diving into specific examples, a reliable process: read the query's intent first ("this looks like it's trying to find X"), then check it against the categories of bugs that show up constantly — NULL handling, JOIN direction and filter placement, GROUP BY correctness, aggregate behavior, and sargability. Say your reasoning out loud as you check each category, rather than staring silently — interviewers are grading the process as much as the final answer.
Query 1: The Silent Anti-Join Bug
SELECT name FROM employees
WHERE department NOT IN (SELECT department FROM archived_departments);What's wrong with it: If archived_departments.department contains even a single NULL, this query silently returns zero rows — for every employee, regardless of the data. NOT IN (a, b, NULL) is logically equivalent to != a AND != b AND != NULL, and any comparison against NULL evaluates to UNKNOWN, which makes the entire AND chain UNKNOWN — never TRUE.
-- Fixed: NOT EXISTS is immune to NULLs in the subquery
SELECT e.name FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM archived_departments a WHERE a.department = e.department
);Full explanation: NULL handling guide.
Query 2: The LEFT JOIN That Secretly Became an INNER JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.status = 'active';What's wrong with it: The intent was clearly to keep every employee, even ones without a department. But putting the filter on d.status in WHERE instead of ON silently discards every row where d is NULL — because WHERE runs after the join, and NULL = 'active' is never true. This turns the LEFT JOIN back into the equivalent of an INNER JOIN.
-- Fixed: move the condition into ON to preserve unmatched left rows
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.status = 'active';Full explanation: JOINs guide.
Query 3: The Missing GROUP BY Column
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;What's wrong with it: name isn't wrapped in an aggregate function and isn't in the GROUP BY list. On PostgreSQL and SQL Server, this simply errors out. On MySQL with relaxed settings, it silently runs and returns an arbitrary, non-deterministic name from within each group — which is worse than an error, because it looks like a valid answer.
-- Fixed: either add name to GROUP BY, or remove it if a per-department aggregate is really the intent
SELECT department, AVG(salary)
FROM employees
GROUP BY department;Full explanation: GROUP BY vs HAVING vs WHERE guide.
Query 4: COUNT(column) vs. COUNT(*) Mismatch
SELECT department, COUNT(phone) AS employee_count
FROM employees
GROUP BY department;What's wrong with it: This looks like it's counting employees per department, but COUNT(phone) only counts rows where phone is non-NULL — any employee without a phone number recorded is silently excluded from the count. The column name (employee_count) makes it look correct, which is exactly why this bug slips through code review.
-- Fixed: COUNT(*) counts every row regardless of NULLs in any specific column
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;Query 5: The Nth Highest Salary That Breaks on Ties
SELECT salary FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;What's wrong with it: If two employees are tied for the highest salary, this returns that same top salary again as the "second highest" — because OFFSET 1 skips one row, not one distinct value. Without testing against tied data specifically, this bug is invisible.
-- Fixed: DISTINCT collapses ties into a single value before offsetting
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;Full explanation: Nth highest salary guide.
Query 6: The Non-Sargable Date Filter
SELECT * FROM orders
WHERE DATE(created_at) = '2026-01-15';What's wrong with it: The query is functionally correct, but wrapping created_at in DATE() makes any index on that column unusable — the database would have to apply the function to every single row before it could compare, defeating the purpose of the index. On a large table, this quietly turns an index lookup into a full scan.
-- Fixed: a range comparison stays sargable and can use an index on created_at
SELECT * FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
AND created_at < '2026-01-16 00:00:00';Full explanation: query optimization guide.
Query 7: The Unguarded Division
SELECT
product_id,
revenue / units_sold AS avg_price
FROM sales_summary;What's wrong with it: If any row has units_sold = 0, this throws a division-by-zero error and can break the entire query (or the entire report, depending on how errors are handled downstream) — a single bad row takes down the whole result set.
-- Fixed: NULLIF turns a zero denominator into NULL, so the division returns NULL instead of erroring
SELECT
product_id,
revenue / NULLIF(units_sold, 0) AS avg_price
FROM sales_summary;Full explanation: NULL handling guide.
Query 8: The Accidental Cartesian Product
SELECT e.name, d.name AS department
FROM employees e, departments d
WHERE e.active = true;What's wrong with it: There's no join condition linking employees to departments at all — this is old-style comma-join syntax with a missing relationship, which silently produces a full Cartesian product: every employee paired with every department. On small test tables this might go unnoticed; on real data, it can return a wildly inflated, meaningless result set.
-- Fixed: explicit JOIN syntax forces you to state the actual relationship
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.active = true;Full explanation: JOINs guide (CROSS JOIN section).
Query 9: The UPDATE That Compounds on Every Re-Run
UPDATE products SET price = price * 1.1;What's wrong with it: This isn't wrong the first time it runs — the danger is that it's not idempotent. If a deployment fails partway through and gets retried, or someone accidentally runs it twice, prices get a 21% increase instead of 10%, compounding silently with each execution. Nothing about the query itself signals this risk.
-- Fixed: compute against a stable base price rather than the mutable current price
UPDATE products SET price = base_price * 1.1 WHERE price != base_price * 1.1;Full explanation: idempotent SQL guide.
Query 10: The Missing PARTITION BY in a "Top N Per Group" Query
SELECT name, department, salary FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn <= 2;What's wrong with it: The intent is almost certainly "top 2 earners per department," but without PARTITION BY department, ROW_NUMBER() ranks across the entire table. This returns only the top 2 earners company-wide, and every other department gets zero rows in the result — a silent, easy-to-miss logic error rather than an obvious one.
-- Fixed: PARTITION BY resets the ranking within each department
SELECT name, department, salary FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn <= 2;Full explanation: top N per group guide.
Query 11: The Date Range That Silently Excludes Same-Day Timestamps
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';What's wrong with it: If order_date is a timestamp (not a plain date), the literal '2026-01-31' is interpreted as midnight — 2026-01-31 00:00:00 — so any order placed later that same day (say, 3pm on January 31st) is silently excluded from what looks like an inclusive full-month range.
-- Fixed: use an explicit half-open range instead of BETWEEN on a timestamp column
SELECT * FROM orders
WHERE order_date >= '2026-01-01 00:00:00'
AND order_date < '2026-02-01 00:00:00';Full explanation: date and time functions guide.
Query 12: The Case-Sensitive Match That Misses Real Matches
SELECT * FROM users WHERE email = 'Alice@Example.com';What's wrong with it: On a case-sensitive collation, this fails to match a stored value of 'alice@example.com' — the two look identical to a human reading the output, but the exact-match comparison treats them as different strings entirely. This is a common cause of "the row is definitely there, why isn't it matching" confusion.
-- Fixed: normalize both sides explicitly for guaranteed case-insensitive matching
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');Full explanation: string functions guide.
How to Present Your Answer in an Interview
State what the query is trying to do, then what it actually does, then why the two diverge — in that order. "This looks like it's meant to keep all employees even without a department, but because the filter is in WHERE instead of ON, it actually drops anyone without one" is a much stronger answer than just presenting the fixed query silently. Naming the specific mechanism (NULL comparison logic, join filter placement, sargability) is what separates a strong answer from a lucky guess.
Common Mistakes When Answering These Questions
- Fixating on style instead of logic — pointing out that a query "could be written more efficiently" when the actual bug is a correctness issue that changes the result entirely.
- Fixing the query silently without explaining the mechanism — interviewers want to hear the reasoning, not just see a corrected query appear.
- Assuming there's only one bug — some interview queries intentionally contain more than one issue; keep checking after finding the first one.
- Not testing the claim mentally against edge cases — a tie, a NULL, a zero, a same-day timestamp. Most of the bugs above are invisible on clean, ordinary data and only surface at an edge case.
Frequently Asked Questions
Are these bugs specific to one database?
No — every bug in this guide is a logic error in the SQL itself, not a database-specific quirk, so the same reasoning applies whether the query runs on PostgreSQL, MySQL, or SQL Server. Where behavior does differ (like MySQL's relaxed GROUP BY handling in Query 3), it's called out explicitly.
What if I can't spot the bug immediately?
Say what you're checking as you check it — "let me look at how NULLs are handled here, then the join direction, then GROUP BY correctness" — rather than going silent. Interviewers are often more interested in a methodical process that eventually finds the issue than an instant guess.
Practice Spotting Bugs
Reading and debugging someone else's SQL is a distinct skill from writing your own from scratch, and it's worth deliberately practicing. Try it hands-on with our SQL practice questions, or work through realistic flawed queries in our case studies. For the broader situational judgment these questions often pair with, see Part 1 and Part 2 of this series.