10 SQL Queries That Look Correct but Produce Wrong Results

1. JOIN fan-out inflating a SUM()

Joining a one-to-many child table before aggregating a value from the parent table repeats that value once per child row. The row count can still look completely normal — only the aggregate value is wrong. Covered in full in Why Is My SQL SUM() Wrong After a JOIN? and How a SQL JOIN Can Quietly Multiply Your Revenue.

2. NOT IN with a NULL in the subquery (live example)

This is the most dangerous entry on this list because the failure mode isn't a wrong number — it's an empty result set that looks like "no matches" instead of a bug. If the subquery inside a NOT IN can return even one NULL, the entire outer condition silently stops matching anything, for every row:

-- Intent: employees who manage nobody (individual contributors)
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
-- Returns ZERO rows the moment any manager_id is NULL — not an error, just silence.

Try it yourself below — same dataset, real engine, both versions runnable:

The fix is NOT EXISTS, which checks row existence directly and is completely unaffected by NULLs in the subquery:

SELECT name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.id
);

3. INNER JOIN dropping rows that should show as zero

An INNER JOIN where a LEFT JOIN was needed silently removes parent rows with no match on the child side — a customer with zero orders vanishes from a revenue report instead of appearing with $0. The query runs fine; it just quietly reports on fewer entities than actually exist. Full comparison: INNER JOIN vs LEFT JOIN vs RIGHT JOIN.

4. Integer division truncating a ratio

On engines where dividing two integer-typed columns performs integer division, completed / total truncates toward zero — a 90% completion rate can come back as 0 if the columns are typed as integers. The fix is casting at least one operand to a float or decimal before dividing:

-- Silently truncates to 0 on engines with integer division:
SELECT completed / total AS completion_rate FROM region_stats;
-- Fixed:
SELECT completed * 1.0 / total AS completion_rate FROM region_stats;

5. = NULL instead of IS NULL

column = NULL never evaluates to TRUE for any row, including rows where the column genuinely is NULL — comparing anything to NULL with = returns UNKNOWN, not TRUE. The query runs, returns zero rows, and raises no error. Full explanation: NULL Handling in SQL.

6. Dates stored as text, sorted alphabetically

A date column typed as text and formatted like 9/1/2026 sorts lexicographically, not chronologically — "10/1/2026" sorts before "9/1/2026" because "1" is a smaller character than "9". An ORDER BY on that column runs cleanly and produces a plausible-looking but chronologically wrong sequence. Store dates as an actual date/timestamp type, or format as YYYY-MM-DD if a text column is unavoidable, so lexicographic order matches chronological order.

7. DISTINCT on the wrong column set

SELECT DISTINCT deduplicates on every selected column as a combination, not on the one column someone had in mind. Adding one more column to the SELECT list — even a timestamp or an ID — can silently un-deduplicate rows that used to look unique, because the new column makes previously-identical rows differ again.

8. Whitespace or case mismatches silently breaking a JOIN key

Joining on a text key like an email or product code where one side has trailing whitespace, inconsistent casing, or a stray tab character causes those specific rows to simply not match — no error, just missing rows in the output, indistinguishable at a glance from rows that genuinely have no counterpart. TRIM() and a consistent UPPER()/LOWER() on both sides of the join condition prevents this class of bug.

9. A correlated subquery correlated on the wrong column

A correlated subquery that references an outer column with a similar but wrong name — especially easy in a query joining several similarly-structured tables — silently correlates on the wrong relationship instead of failing, since the wrong column reference is still valid SQL as long as it resolves to something in scope. The result looks like a normal per-row value; it's just the wrong per-row value. See Correlated Subqueries Explained for how these should be structured.

10. Rounding each row before summing

Rounding a monetary value at the row level before summing produces a different total than summing first and rounding once, because each row's rounding error compounds across the full result set. The gap is usually small per row and easy to dismiss as "rounding," but it accumulates linearly with row count and can become material on a large report. Round once, at the very end of the calculation, not on every row along the way.

Challenge: fix the manager query

Same employees dataset used in the live example above. Three tasks — find the people with no manager, find the individual contributors correctly, and compute a department average that excludes managers.