Why SQL Queries Return Unexpected Results

1. NULLs behaving strangely

The number-one source of "impossible" results. NULL means unknown, so any comparison with it is UNKNOWN — and WHERE silently drops those rows. Watch for = NULL (matches nothing), <> conditions dropping NULL rows, and NOT IN returning zero rows when the list contains a NULL.

-- ❌ NOT IN returns NOTHING if any manager_id is NULL
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- ✅ NOT EXISTS is NULL-safe
SELECT e.name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id);

Full breakdown: NULL handling in SQL (IS NULL vs = NULL).

2. Duplicated / inflated rows from a JOIN

If your row count or your SUM is suddenly too big, a one-to-many JOIN is multiplying rows. Each parent row is repeated once per child, so totals over the parent are inflated.

-- ❌ order total counted once per line item
SELECT o.customer_id, SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;

-- ✅ aggregate at the right grain, or COUNT(DISTINCT ...)
SELECT customer_id, SUM(total)
FROM orders GROUP BY customer_id;

More on this in GROUP BY mistakes that break SQL queries and INNER vs LEFT vs RIGHT JOIN.

3. LEFT JOIN acting like INNER JOIN

You wrote a LEFT JOIN but rows went missing. A condition on the right table in WHERE removes the NULL rows, quietly converting it into an inner join. Put the condition in ON instead.

-- ✅ keep unmatched left rows: condition goes in ON
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id AND o.amount > 100;

4. Filtering in the wrong clause

WHERE, HAVING, and ON do different jobs. Filtering rows in HAVING, or trying to filter aggregates in WHERE, or referencing a SELECT alias in WHERE, all produce surprises (or errors). WHERE filters rows before grouping; HAVING filters groups after; ON defines what matches in a join.

See SELECT vs WHERE vs HAVING and SQL execution order for the why.

5. Integer division truncation

Dividing two integers throws away the remainder. 5 / 2 is 2, not 2.5 — so percentages and averages come out wrong. Cast one operand to a decimal.

SELECT 5 / 2;                 -- 2   (integer division)
SELECT 5.0 / 2;               -- 2.5 (one operand is decimal)
SELECT passed * 100.0 / total AS pass_rate FROM exams;

6. Implicit type conversion

When you compare different types, the database converts one side — sometimes surprisingly. Numbers stored as text sort lexically ('10' before '2'); a leading-zero code like '01234' becomes 1234 if compared as a number; dates compared as strings mis-sort.

-- ❌ zip is text; the number 01234 becomes 1234 and won't match
SELECT * FROM addresses WHERE zip = 01234;

-- ✅ compare text to text
SELECT * FROM addresses WHERE zip = '01234';

7. AND / OR precedence

AND binds tighter than OR. Without parentheses, A OR B AND C is read as A OR (B AND C) — which is probably not what you meant.

-- ❌ Reads as: country='US' OR (country='CA' AND active=1)
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' AND active = 1;

-- ✅ Parenthesize the OR group
SELECT * FROM users
WHERE (country = 'US' OR country = 'CA') AND active = 1;

8. Missing ORDER BY (and LIMIT)

SQL guarantees no order without ORDER BY. Results can look sorted, then quietly reshuffle after an index or version change. This is especially dangerous with LIMIT: LIMIT 10 without ORDER BY returns ten arbitrary rows, not "the top 10." (See ORDER BY before or after GROUP BY?.)

9. UNION vs UNION ALL

Fewer rows than you expected after combining queries? UNION removes duplicate rows (and does extra work to do so). If you want every row — including duplicates — use UNION ALL.

SELECT city FROM customers
UNION ALL              -- keep duplicates (faster)
SELECT city FROM suppliers;

The 60-second checklist