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

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:

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:

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

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:

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

The fix:

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

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

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

-- 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:

-- 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.

-- 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:

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):

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

7. IN vs EXISTS with NULL

-- 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:

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

8. UNION vs UNION ALL

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

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

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:

WHERE id = 12345;   -- not '12345'

Same applies to wrapping a column in a function:

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

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.