The WHERE clause filters rows, returning only those that match a condition. It's fundamental to retrieving specific data rather than entire tables, and supports a rich set of operators and conditions.
users age ;
users country ;
users age active ;
users country country ;
users active;
Conditions use comparison operators and combine with AND/OR/NOT (use parentheses to control precedence in complex conditions).
-- BETWEEN — a range (inclusive)
WHERE age BETWEEN 18 AND 65
-- IN — match any value in a list
WHERE country IN ('US', 'CA', 'UK')
-- LIKE — pattern matching (% = any chars, _ = one char)
WHERE name LIKE 'A%' -- starts with A
WHERE email LIKE '%@gmail.com' -- ends with @gmail.com
WHERE code LIKE '_X_' -- exactly 3 chars with X in the middle
BETWEEN (ranges), IN (value lists), and LIKE (pattern matching with %/_ wildcards) handle common filtering needs concisely.
-- ❌ WRONG — you CANNOT compare to NULL with = (NULL is "unknown")
WHERE email = NULL -- returns NOTHING (never true)
-- ✅ use IS NULL / IS NOT NULL
WHERE email IS NULL
WHERE email IS NOT NULL
Critical: NULL represents "unknown," and you can't compare to it with = (email = NULL is never true). You must use IS NULL/IS NOT NULL — a common mistake.
WHERE conditions on INDEXED columns are fast; on un-indexed columns the database
must scan every row. For frequently-filtered columns, add an index.
Also: functions on a column (WHERE UPPER(name) = 'X') can prevent index use.
The WHERE clause is fundamental to querying — it's how you retrieve specific data rather than entire tables, which is necessary for virtually every real query, so understanding it is essential everyday knowledge.
Knowing the operators (comparisons, AND/OR/NOT with proper precedence) and the useful special operators (BETWEEN for ranges, IN for value lists, LIKE for pattern matching) covers the spectrum of filtering needs.
A particularly important and commonly-mistaken point is NULL handling: because NULL means "unknown," you cannot compare to it with = (a frequent bug where WHERE col = NULL silently returns nothing), and must use IS NULL/IS NOT NULL instead — understanding this prevents a common, confusing error.
It's also valuable to know the performance connection: filtering on indexed columns is fast while un-indexed columns require scanning every row (so frequently-filtered columns benefit from indexes), and that applying functions to a column in WHERE can prevent index use.
Since filtering data with WHERE is one of the most common database operations, and since understanding the operators, NULL handling (a frequent pitfall), and the index/performance implications is foundational to writing correct, efficient queries, mastering the WHERE clause is core, must-know knowledge for working with SQL.