SQL Gotchas: JOIN Types, NULL Behavior, and WHERE vs HAVING

SQL bugs are uniquely dangerous because they often produce silent wrong answers — the query runs fine, the data looks plausible, but the numbers are wrong. By the time you notice, that data may have already shipped to a customer report.

These are the SQL gotchas that produce silent wrong answers.

1. LEFT JOIN + WHERE = secretly an INNER JOIN

``sql SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'completed'; ``

This looks like "all users with their completed orders, or no order info if they have none." It's actually equivalent to INNER JOIN. Users with no orders have o.status = NULL, and NULL = 'completed' is unknown/false, so they're filtered out.

The fix: put the condition in the JOIN clause:

``sql SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'; ``

Or move the WHERE to handle NULL:

``sql WHERE o.status = 'completed' OR o.user_id IS NULL ``

This is the single most common SQL bug in production code.

2. NULL doesn't equal anything — including itself

``sql NULL = NULL -- UNKNOWN (treated as FALSE) NULL != NULL -- UNKNOWN NULL = 0 -- UNKNOWN NULL = '' -- UNKNOWN ``

To check for NULL, use IS NULL / IS NOT NULL. Never = NULL or != NULL.

This breaks intuitive queries:

``sql SELECT * FROM users WHERE country != 'USA'; -- Excludes rows where country IS NULL ``

The fix:

``sql SELECT * FROM users WHERE country != 'USA' OR country IS NULL; ``

3. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

``sql COUNT(*) -- all rows (including NULLs) COUNT(column) -- non-NULL values in column COUNT(DISTINCT col) -- unique non-NULL values ``

This is the #2 source of off-by-N bugs in analytics queries. If COUNT(email) returns 1000 but COUNT(*) returns 1050, you have 50 users with NULL emails.

4. WHERE vs HAVING

• WHERE filters rows before grouping
• HAVING filters groups after grouping
• Aggregate functions can ONLY appear in HAVING, never in WHERE

```sql -- Wrong: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id WHERE COUNT(*) > 5; -- ERROR: aggregate in WHERE

-- Right: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5; ```

A common related bug: filtering on aggregates via subquery instead of HAVING is much slower:

```sql -- Slower: SELECT * FROM ( SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id ) sub WHERE n > 5;

-- Faster: SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5; ```

5. GROUP BY mode strictness

MySQL (in non-strict mode) lets you mix aggregates with non-aggregate columns that aren't in GROUP BY — and silently picks an arbitrary row's value.

```sql -- MySQL non-strict: works, value of 'name' is random SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;

-- Strict (ANSI) SQL: error — name must be in GROUP BY or aggregated ```

In MySQL 5.7+, the default is strict mode (ONLY_FULL_GROUP_BY). Legacy queries break on upgrade. Either add columns to GROUP BY, wrap them in MAX()/MIN(), or use ANY_VALUE() to opt out.

6. Order of execution is NOT the order written

SQL clause order written: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

Logical execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

This explains why:

``sql SELECT price * 0.9 AS discounted FROM products WHERE discounted > 10; -- ERROR: discounted is unknown in WHERE -- (WHERE runs before SELECT) ``

You can use the alias in ORDER BY (which runs after SELECT):

``sql SELECT price * 0.9 AS discounted FROM products ORDER BY discounted DESC; -- Works ``

7. IN vs EXISTS with NULL

``sql -- If subquery returns NULL among results: SELECT * FROM users WHERE id NOT IN (SELECT manager_id FROM employees); -- Returns NO rows if any manager_id is NULL ``

NOT IN (... NULL ...) always evaluates to unknown/false. Use NOT EXISTS instead — it handles NULL correctly:

``sql SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.manager_id = u.id ); ``

8. UNION vs UNION ALL

``sql SELECT name FROM employees UNION SELECT name FROM contractors; -- Deduplicates (sort + remove dupes) ``

UNION always deduplicates (involves a sort). UNION ALL doesn't dedupe and is significantly faster.

If you know your inputs have no overlap, always use UNION ALL. It's a free perf win.

9. COALESCE vs IFNULL vs ISNULL

``sql COALESCE(a, b, c, ...) -- returns first non-NULL (ANSI SQL) IFNULL(a, b) -- MySQL: a if not NULL, else b ISNULL(a, b) -- SQL Server: a if not NULL, else b ISNULL(a) -- MySQL: returns 1 if a is NULL, 0 otherwise (different!) ``

COALESCE is portable. IFNULL and ISNULL are vendor-specific and behave differently across databases. Stick with COALESCE.

10. Implicit type conversions destroy indexes

``sql SELECT * FROM users WHERE id = '12345'; ``

If id is INT and you compare to a string, the database may do a full table scan instead of using the index, because it has to convert every row's int to a string for comparison. Or worse — convert your string to int and use the index (depends on DB).

Always use the right type:

``sql WHERE id = 12345; -- not '12345' ``

Same applies to wrapping a column in a function:

``sql WHERE YEAR(created_at) = 2026; -- index unusable WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'; -- index usable ``

11. SELECT * in subqueries / views

SELECT * is fine for ad-hoc queries but causes silent breakage when:

• The underlying table adds a column → row size grows unexpectedly in cached query plans
• The underlying table renames a column → app code breaks
• A JOIN starts returning unexpected columns

For production queries, always enumerate columns explicitly.

12. LIMIT without ORDER BY is non-deterministic

``sql SELECT * FROM products LIMIT 10; ``

You may get different 10 rows each time. Always pair LIMIT with ORDER BY for reproducible results.

Need help with a slow or broken query?

Most production SQL problems come down to one of these gotchas plus a busy database. If you've got reports that don't match across systems, queries that suddenly got slow, or a database migration that's stalled, we can help.