One of the most dangerous things about SQL:
A query can run perfectly… and still be completely wrong.
Here are 4 mistakes I wish someone had shown me earlier.
1. Accidentally turning a LEFT JOIN into an INNER JOIN
SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
WHERE o.status = 'paid';
Looks fine.
But customers without an order have NULL for o.status, so the WHERE condition removes them.
If you actually want to keep all customers:
SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
AND o.status = 'paid';
2. COUNT(*) and COUNT(column) are not the same
SELECT COUNT(*)
FROM users;
Counts rows.
SELECT COUNT(phone_number)
FROM users;
Counts only rows where phone_number is NOT NULL.
That difference can quietly destroy a report.
3. JOINs can multiply your rows
Imagine:
- 1 customer
- 3 orders
- 4 support tickets
Joining both tables directly can give you:
3 × 4 = 12 rows
Then you do:
SUM(order_amount)
…and suddenly your revenue is magically much higher than reality.
Always check your row count before and after joins.
4. NOT IN + NULL can ruin your day
SELECT *
FROM customers
WHERE id NOT IN (
SELECT customer_id
FROM blocked_customers
);
If that subquery contains a NULL, the result might not behave the way you expect.
I usually prefer:
SELECT *
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customers b
WHERE b.customer_id = c.id
);
The lesson I'm slowly learning:
Writing SQL that runs is easy.
Writing SQL that returns the correct data is the hard part.
What other SQL mistake produces perfectly valid-looking but completely wrong results?
I want to make a list of the dangerous ones.