What is the difference between = and IS NULL in SQL?

= uses two-valued comparison logic. NULL represents an unknown value, so NULL = NULL evaluates to UNKNOWN (not TRUE). IS NULL is a special predicate that returns TRUE only when the value is NULL. The practical consequence: WHERE col = NULL will never return any rows.

Mention three-valued logic (TRUE, FALSE, UNKNOWN).

Why can mixing AND and OR without parentheses cause bugs?

AND has higher operator precedence than OR. So `A OR B AND C` is parsed as `A OR (B AND C)`. If your intent was `(A OR B) AND C`, you'll get different — and usually more — rows than expected. Always parenthesize mixed conditions.

Give a concrete example with department and salary.

Why does NOT IN fail when the subquery returns a NULL?

NOT IN expands to: col != val1 AND col != val2 AND ... If any value in the list is NULL, the comparison col != NULL evaluates to UNKNOWN, making the entire AND chain UNKNOWN. SQL filters out UNKNOWN conditions, so zero rows are returned. NOT EXISTS is immune to this because it only checks existence, not equality.

This is a classic SQL trap. Say 'three-valued logic' and 'NOT EXISTS is the safe alternative'.

When is LIKE sargable vs. non-sargable?

LIKE 'value%' (trailing wildcard only) is sargable — the database can perform an index range scan from the fixed prefix. LIKE '%value' or LIKE '%value%' (leading wildcard) is NOT sargable — the engine must scan every row to check the pattern. For substring search at scale, use full-text indexes.

Remember: leading % = full scan, trailing % = index seek.

What is the difference between ANY and ALL in SQL?

col > ANY (subquery) returns TRUE if col is greater than at least one value in the subquery — equivalent to col > (SELECT MIN(...)). col > ALL (subquery) returns TRUE only if col is greater than every value — equivalent to col > (SELECT MAX(...)). The MIN/MAX rewrite is usually clearer and can be more efficient.

Mention the MIN/MAX equivalents as a cleaner alternative.

How does OR across different columns affect index usage?

When OR connects predicates on different columns (WHERE a = 1 OR b = 2), most optimizers cannot use a single index for both branches simultaneously. The solution is to rewrite as UNION ALL — each branch can then independently use its own index. Some databases (MySQL 5.0+) support Index Merge optimization, but UNION ALL is more portable and predictable.

Mention UNION ALL as the standard solution, and Index Merge as a database-specific optimization.