Debugging Common SQL Logic Errors: Why Your Query is Wrong

The most dangerous SQL bug isn't a syntax error. It's a Logic Error.

The query runs perfectly. The database is happy. But the report says we made $10 million yesterday instead of $10,000.

Here are 3 classic ways to accidentally destroy your data integrity.

A Real Business Scenario: The KPI Dashboard That Quietly Drifted

A typical debugging story is not "the query crashed". It is "the weekly dashboard stopped matching finance".

Imagine a growth team dashboard that combines:

• orders
• refunds
• user signups
• campaign attribution

All of those tables are individually valid. The problem starts when one join multiplies rows, one rate calculation truncates decimals, and one exclusion subquery swallows valid records because of a NULL.

That is why logic debugging deserves a repeatable workflow: inspect row counts, isolate each step, and verify the intended grain before trusting any aggregate.

1. The Fan Trap (Exploding Joins)

This happens when you join two tables that both have a "Many" relationship to your main table.

Scenario: You want to sum Sales and Returns for each Product.

• Products: 1 row
• Sales: 3 rows (sold 3 items)
• Returns: 2 rows (returned 2 items)

If you join Products -> Sales AND Products -> Returns in one query, the rows multiply. 3 Sales x 2 Returns = 6 Rows. Your sums will be double or triple the real values.

See it in Action (The Bug)

Wait!

• Real Sales: 300. Query says: 600.
• Real Returns: 100. Query says: 300.

The Fix: Aggregate before joining, or use correlated subqueries.

The Wrong "Fix": Hiding the Bug with DISTINCT

Developers under deadline pressure often try this next:

SELECT DISTINCT
  p.name,
  SUM(s.amount) as total_sales,
  SUM(r.amount) as total_returns
FROM products p
LEFT JOIN sales s ON p.id = s.product_id
LEFT JOIN returns r ON p.id = r.product_id
GROUP BY p.name;

That does not repair the logic. DISTINCT removes duplicate output rows after the join has already multiplied the underlying combinations. If the joined dataset is wrong, the aggregate is already wrong.

The real fix is to restore the intended grain before the final join:

WITH sales_by_product AS (
  SELECT product_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY product_id
),
returns_by_product AS (
  SELECT product_id, SUM(amount) AS total_returns
  FROM returns
  GROUP BY product_id
)
SELECT
  p.name,
  COALESCE(s.total_sales, 0) AS total_sales,
  COALESCE(r.total_returns, 0) AS total_returns
FROM products p
LEFT JOIN sales_by_product s ON p.id = s.product_id
LEFT JOIN returns_by_product r ON p.id = r.product_id;

2. Integer Division

In many SQL dialects (like SQL Server, PostgreSQL), dividing two integers returns an integer. It truncates the decimal.

SELECT 1 / 2; -- Returns 0 !
SELECT 99 / 100; -- Returns 0 !

The Fix: Multiply by 1.0 to force decimal math.

SELECT 1.0 * 1 / 2; -- Returns 0.5

This matters whenever you calculate conversion rates, return rates, or averages from integer columns. A query can look completely reasonable and still collapse small percentages to 0.

3. The Dangerous NOT IN with NULLs

If you use NOT IN (...) and the list contains a single NULL, the result is Empty.

Why? Because 1 != NULL is UNKNOWN. SQL can't confirm "1 is NOT inside a list containing Unknown".

The Trap

Result: NO rows are returned, even "GoodUser". The entire query fails silently because of one NULL in the subquery.

The Fix:

• Use NOT EXISTS (safest).
• Or add WHERE user_id IS NOT NULL inside the subquery.

Performance and Boundary Notes

Logic bugs and performance bugs often reinforce each other.

• Fan traps inflate row counts, which makes aggregates slower and wrong at the same time.
• A quick rewrite with nested subqueries may fix logic but introduce repeated scans if the engine cannot optimize it well.
• Debug queries that use SELECT * across many joined tables become harder to inspect and more expensive to run.

A good debugging pattern is:

1. check the grain you expect
2. count rows after each join
3. inspect a tiny sample before reintroducing aggregates
4. run EXPLAIN only after the result shape is logically correct

If you optimize a logically wrong query first, you only get the wrong answer faster.

When NOT to Patch the Symptom

Do not use these shortcuts as a substitute for understanding the grain of the data:

• adding DISTINCT because totals look too large
• wrapping everything in COALESCE before you know why NULL appeared
• converting every denominator to decimal without checking whether the numerator and denominator are even aligned

Those moves can make a broken query look calmer without making it correct. The right question is always: "What should one row represent at this stage of the query?"

Official References

• PostgreSQL subquery expression documentation for the formal NOT IN and NOT EXISTS semantics, including NULL behavior.
• PostgreSQL mathematical operator documentation for numeric operator behavior that affects integer division and percentage calculations.
• SQLite expression documentation for expression evaluation rules, including NULL handling and operator behavior.

Related Articles

• Common SQL Anti-Patterns for the broader family of query shapes that are risky even when they do not immediately produce wrong numbers.
• Dynamic SQL Best Practices for cases where the debugging target is generated SQL rather than a hand-written statement.
• Reading SQL Execution Plans for the next step when the query is logically correct but still behaves badly at runtime.

Conclusion

SQL is literal. It does exactly what you ask, not what you want. Always inspect your intermediate row counts when doing joins, and be paranoid about NULLs!