WHERE and Comparison Operators

Concept

WHERE filters rows before grouping and before the select list is computed. For each row the condition is evaluated and must be TRUE for the row to survive - not FALSE, and importantly not UNKNOWN.

Syntax

SELECT column_list
FROM   table_name
WHERE  condition;

Example

SELECT first_name, last_name, salary, hire_date
FROM   employees
WHERE  salary >= 70000;

SELECT first_name, hire_date
FROM   employees
WHERE  hire_date >= '2021-01-01'
  AND  hire_date <  '2023-01-01';

SELECT first_name, status
FROM   employees
WHERE  status <> 'active';

Explanation

The date filter deliberately uses >= start AND < day_after_end rather than BETWEEN. With a DATE column either form works, but the moment the column becomes a DATETIME, BETWEEN '2021-01-01' AND '2022-12-31' silently loses everything that happened on 31 December after midnight. The half open range is correct for both types.

Keep the column bare

-- Slow: the function hides the column from the index
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Fast: a plain range the index can seek
SELECT * FROM orders
WHERE  order_date >= '2024-01-01'
  AND  order_date <  '2025-01-01';

This is called making a predicate sargable (search argument able). If the indexed column appears wrapped in a function, arithmetic or a cast, the engine cannot use the index to jump straight to the rows and falls back to reading the whole table.

Important rules

• A row is returned only when the condition is TRUE. UNKNOWN - which is what any comparison with NULL produces - is not enough.
• WHERE runs before SELECT, so select list aliases are not available in it.
• WHERE cannot contain an aggregate function. Filtering on COUNT(*) belongs in HAVING.
• String comparison depends on collation: 'ASHA' = 'asha' is true under a case insensitive collation and false under a case sensitive one.
• Comparing different types forces an implicit conversion, which can be both slow and surprising.

Common mistakes

• Writing WHERE dept_id = NULL. It matches nothing, ever. Use IS NULL.
• Expecting WHERE status <> 'active' to include rows where status is NULL. It does not.
• Wrapping the filtered column in UPPER(), DATE() or CAST() and losing the index.
• Using BETWEEN on a timestamp column and dropping the final day.

Best practices

• Filter as early and as narrowly as possible; every later stage does less work.
• Write half open date ranges: >= start AND < end.
• Keep indexed columns bare on one side of the comparison.
• Compare like with like - dates to dates, numbers to numbers, no quoted integers.

Practice

1. Find every employee earning between 60000 and 90000 who is still active.
2. Rewrite WHERE MONTH(hire_date) = 6 AND YEAR(hire_date) = 2024 as a sargable range.
3. Why does WHERE email <> 'sara@example.com' miss the employee whose email is NULL?