WHERE, Filtering and Pattern Matching

WHERE decides which rows survive, and it runs before SELECT — so it sees the table's columns, not your aliases or aggregates. Beyond the syntax there are exactly two things that separate a good answer from a passing one: the NOT IN NULL trap, and knowing when a predicate can use an index.

The operators

SELECT * FROM employees
WHERE salary > 50000
  AND department IN ('Sales', 'Engineering')
  AND hire_date BETWEEN '2020-01-01' AND '2023-12-31'
  AND manager_id IS NOT NULL
  AND first_name LIKE 'A%';

BETWEEN on a DATETIME is the trap: BETWEEN '2024-01-01' AND '2024-01-31' misses everything on the 31st after midnight, because the literal means 00:00:00. Use >= '2024-01-01' AND < '2024-02-01' for date ranges and the class of bug disappears.

The NOT IN NULL trap

The single most-asked SQL gotcha:

-- If ANY manager_id is NULL, this returns ZERO rows. Always.
SELECT * FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

NOT IN expands to id <> a AND id <> b AND id <> NULL, and id <> NULL is UNKNOWN, not TRUE. AND with UNKNOWN can never be TRUE, so every row is filtered out. The query is not wrong-looking; it is silently empty.

Three fixes, in order of preference:

-- 1. NOT EXISTS — immune, and usually the best plan
SELECT e.* FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id
);

-- 2. Exclude the NULLs explicitly
WHERE employee_id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

-- 3. LEFT JOIN … IS NULL, the anti-join idiom
SELECT e.* FROM employees e
LEFT JOIN employees m ON m.manager_id = e.employee_id
WHERE m.employee_id IS NULL;

IN with NULLs is fine — it only fails to match, it does not nullify the whole predicate. It is specifically the negation that breaks. See NULL handling for the wider rules.

LIKE and wildcards

WHERE name LIKE 'A%'        -- starts with A
WHERE name LIKE '%son'      -- ends with son
WHERE name LIKE '%ann%'     -- contains ann
WHERE name LIKE '_ohn'      -- exactly one char, then ohn
WHERE name LIKE 'A%' ESCAPE '\'   -- when you need a literal % or _

% matches any number of characters including none; _ matches exactly one.

Only a leading-anchored pattern can use a B-tree index. LIKE 'A%' can — the index is

sorted, so the engine seeks straight to the A's. LIKE '%son' cannot, because the sort order says nothing about suffixes, so it is a full scan. That difference is the standard follow-up, and the answer for suffix search is to store a reversed column and index that, or to use a trigram index (PostgreSQL's pg_trgm) or full-text search.

For anything more structured than a wildcard, use regular expressions — REGEXP in MySQL, ~ in PostgreSQL — but be aware they never use a plain index.

Sargability: don't wrap the column

A predicate is sargable when the engine can use an index for it. Wrapping the indexed column in a function destroys that:

-- Not sargable: YEAR() must be computed for every row
WHERE YEAR(hire_date) = 2024;

-- Sargable: an index on hire_date takes it straight to the range
WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

Same result, and on a large table the difference is a full scan versus an index seek. The general rule: **keep the column bare on one side of the comparison and put the arithmetic on the other. `WHERE salary 12 > 100000 becomes WHERE salary > 100000 / 12`.

This is the point where a filtering question turns into a performance question, and volunteering it is what makes the answer look senior.

Three-valued logic in brief

SQL predicates evaluate to TRUE, FALSE or UNKNOWN, and WHERE keeps only TRUE.

NULL = NULL      → UNKNOWN   (not TRUE)
NULL <> 5        → UNKNOWN
TRUE  OR UNKNOWN → TRUE
FALSE AND UNKNOWN → FALSE
TRUE  AND UNKNOWN → UNKNOWN

That is the whole mechanism behind the NOT IN trap, and behind "why did my <> filter drop the NULL rows I expected to keep".

Mistakes that cost the interview

• `= NULL` instead of `IS NULL`. It is never true.
• `NOT IN` over a subquery that can produce NULL.
• `BETWEEN` on timestamps, losing the last day.
• `YEAR(col) = …` or `UPPER(col) = …` on an indexed column.
• Assuming `LIKE` is case-insensitive. It depends on the column's collation — in MySQL

it usually is, in PostgreSQL it is not.

• Filtering an aggregate in `WHERE`. That is

`HAVING`.

Practise these on the SQL sheet.