Using = NULL instead of IS NULL

Wrong: WHERE deleted_at = NULL

Correct: WHERE deleted_at IS NULL

Always use IS NULL or IS NOT NULL for NULL comparisons.

= NULL always evaluates to UNKNOWN in three-valued logic, never TRUE — so the condition silently excludes all rows.

Mixing AND and OR without parentheses

Wrong: WHERE dept='Sales' OR dept='Mktg' AND salary > 80000

Correct: WHERE (dept='Sales' OR dept='Mktg') AND salary > 80000

Always add explicit parentheses when combining AND and OR.

AND has higher precedence than OR. `A OR B AND C` is parsed as `A OR (B AND C)`, not `(A OR B) AND C`.

NOT IN with a subquery that may return NULL

Dangerous: WHERE id NOT IN (SELECT nullable_col FROM ...)

Safe: WHERE NOT EXISTS (SELECT 1 FROM ... WHERE col = id)

Use NOT EXISTS instead of NOT IN when the subquery column is nullable.

If the subquery returns any NULL row, NOT IN evaluates every comparison as UNKNOWN, and zero rows are returned.

Leading wildcard LIKE causing full table scans

Slow: WHERE name LIKE '%phone'

Fast: WHERE name LIKE 'Samsung%'

Use trailing wildcards LIKE 'value%' or full-text search for substring matching.

LIKE '%value' cannot use a B-tree index — it must scan every row.

Using BETWEEN on DATETIME without accounting for end-of-day

Tricky: BETWEEN '2024-01-01' AND '2024-01-31'

Safe: >= '2024-01-01' AND < '2024-02-01'

Use explicit >= and < for date ranges on DATETIME columns.

BETWEEN '2024-01-01' AND '2024-01-31' misses rows with timestamp '2024-01-31 14:30:00' if the column is DATETIME.

OR on multiple indexed columns preventing index use

Slow: WHERE category_id = 5 OR status = 'active'

Fast: SELECT ... WHERE category_id = 5

UNION ALL

SELECT ... WHERE status = 'active' AND category_id != 5

Rewrite as UNION ALL to allow each branch to use its own index.

OR across different columns (WHERE a = 1 OR b = 2) often prevents the optimizer from using an index on either column.