SQL WHERE Clause
SQL · Filtering Rows
Theory
WHERE filters rows. The engine checks the condition for each row coming from FROM and keeps only rows where it is TRUE. Conditions use comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT) and special tests such as BETWEEN, IN, LIKE and IS NULL. For example, WHERE dept = 'IT' keeps only IT employees.
SQL uses three-valued logic: a comparison can be TRUE, FALSE or UNKNOWN, and anything compared with NULL is UNKNOWN. WHERE keeps only TRUE rows, so rows with NULL in the tested column silently drop out. WHERE runs before GROUP BY and SELECT, so it cannot use aggregates like SUM() (that is HAVING's job) or column aliases defined in SELECT. In UPDATE and DELETE, WHERE decides which rows are changed.
Why we use it
Tables hold far more rows than any single question needs. WHERE narrows the data to just the relevant rows early, which makes results correct and lets the engine use indexes to read less.
When to use it
Use WHERE whenever you filter on individual row values. To filter on aggregates such as COUNT(*) > 5, use HAVING after GROUP BY. Always double-check WHERE in UPDATE and DELETE, since it controls what gets changed.
How it works
- Query starts: FROM employees
- Read the next row
- Evaluate the condition: TRUE, FALSE or UNKNOWN
- Is the result TRUE? Yes: Keep the row for the next step. No: Discard the row (FALSE or UNKNOWN).
- Repeat until all rows are read
- Pass kept rows to SELECT
Syntax
SELECT columns FROM table
WHERE condition;Example
SELECT * FROM employees WHERE department = 'IT';Key points
- Evaluated before GROUP BY and SELECT
- Supports comparison, logical, range, list and pattern operators
- Keeps only rows where the condition is TRUE; NULL comparisons are UNKNOWN
- Cannot use aggregates like SUM(); use HAVING for that
- Cannot reference SELECT aliases in most engines