SELECT * FROM employees WHERE manager_id = NULL;

SELECT * FROM employees WHERE manager_id IS NULL;

NULL represents an unknown value. Any comparison with NULL evaluates to UNKNOWN — including NULL = NULL. UNKNOWN is treated by WHERE the same as FALSE, so no rows are returned. The correct operators are IS NULL and IS NOT NULL.

If you see WHERE col = NULL in a code review, flag it immediately. Enable SQL linting rules that warn on = NULL comparisons.

Critical

Returns 0 rows — no error thrown

SELECT * FROM orders

WHERE status = 'pending'

OR status = 'processing'

AND total > 1000;

SELECT * FROM orders

WHERE (status = 'pending' OR status = 'processing')

AND total > 1000;

AND has higher operator precedence than OR. The original query is parsed as "status = 'pending' OR (status = 'processing' AND total > 1000)" — returning every pending order of any amount. The fix is explicit parentheses around the OR group.

Always use explicit parentheses when mixing AND and OR. Treat it as a non-negotiable coding standard.

Critical

Returns all pending orders regardless of total — no error thrown

SELECT * FROM orders WHERE YEAR(created_at) = 2024 AND MONTH(created_at) = 6;

SELECT * FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

Applying YEAR() and MONTH() to the created_at column makes both predicates non-sargable. The database must compute these functions for every row, bypassing the index. The range rewrite keeps the column bare, allowing an index range scan.

Before writing a date function in WHERE, always ask: can I express this as a range on the raw column? Almost always yes.

Critical

No error — correct results, but 50-1000x slower on indexed columns

SELECT * FROM customers

WHERE id NOT IN (

SELECT customer_id FROM fraud_reviews

);

SELECT c.*

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM fraud_reviews fr

WHERE fr.customer_id = c.id

);

NOT IN checks whether the value is not equal to any value in the list. NULL = anything evaluates to UNKNOWN, so NOT IN (list containing NULL) produces UNKNOWN for every outer row — all rows are excluded. NOT EXISTS checks for the absence of a correlated row and correctly handles NULLs.

Never use NOT IN with a subquery unless you have a NOT NULL constraint on the subquery column. Default to NOT EXISTS for anti-join patterns.

Critical

Returns 0 rows if fraud_reviews.customer_id has any NULL — no error thrown

SELECT * FROM orders WHERE customer_id = '10042'; -- customer_id is INT

SELECT * FROM orders WHERE customer_id = 10042;

When comparing an INT column against a VARCHAR literal, the database engine must cast every row's column value to perform the comparison. In MySQL, the implicit cast is applied to the column side, making the predicate non-sargable and bypassing the index.