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.
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.
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
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 NULLinside the subquery.
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!