SQL Reference ยท Statements and Clauses
SQL WHERE clause
Reference for the SQL WHERE clause: filter rows with comparisons, AND/OR, IN, BETWEEN, LIKE and IS NULL, plus NULL pitfalls and WHERE versus HAVING.
The WHERE clause filters rows. For each row produced by FROM, the database evaluates the condition and keeps the row only if the result is true. Rows where the condition is false, or unknown because of NULL, are dropped.
WHERE runs before grouping and before the select list. That is why you cannot use an aggregate such as COUNT(*) or a select-list alias in it; use HAVING for conditions on groups.
SELECT name, price FROM products WHERE price BETWEEN 10 AND 30 AND stock > 0;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM table WHERE condition;
| Predicate | Example | Meaning |
|---|---|---|
| Comparison | price >= 20 |
=, <>, <, >, <=, >=. See comparison operators. |
| Range | price BETWEEN 10 AND 30 |
Inclusive on both ends. See BETWEEN. |
| List | status IN ('pending', 'shipped') |
Matches any value in the list. See IN. |
| Pattern | name LIKE 'Co%' |
% matches any run of characters, _ one character. See LIKE. |
| NULL test | email IS NULL |
The only correct way to test for NULL. See IS NULL. |
| Combine | a AND (b OR c) |
See AND, OR and NOT. |
| Subquery | EXISTS (SELECT ...) |
See EXISTS. |
Returns: the subset of rows for which the condition is true.
Dialect differences
- Case sensitivity of strings:
=is case-sensitive in SQLite, PostgreSQL and Oracle. MySQL and SQL Server compare case-insensitively with their default collations. LIKEis case-insensitive for ASCII in SQLite, case-sensitive in PostgreSQL (useILIKE), and follows the collation in MySQL and SQL Server.- Booleans: PostgreSQL and MySQL accept
WHERE is_active; SQL Server and Oracle (before 23ai) need a comparison such asWHERE is_active = 1. - Standard
<>:!=also works in all five dialects covered here.
-- PostgreSQL: case-insensitive pattern SELECT name FROM products WHERE name ILIKE '%speaker%';
Usage notes
- Three-valued logic. A comparison with NULL yields unknown, and
WHEREdiscards unknown rows.x <> 1does not return rows wherexis NULL. See NULL and three-valued logic. - Precedence.
ANDbinds tighter thanOR. Use parentheses whenever you mix them. - Do not wrap indexed columns in functions.
WHERE lower(email) = '[email protected]'cannot use a plain index onemail; see sargable predicates. - Select aliases are not visible.
SELECT price * 2 AS double_price ... WHERE double_price > 50fails in standard SQL; repeat the expression or use a subquery. - Strings use single quotes. Double quotes mean identifiers in standard SQL.
UPDATEandDELETEuse the sameWHERE. Forgetting it affects every row, so write theWHEREfirst.
Related
FROM,HAVING,AND,ORandNOT,IS NULL,LIKE- Lessons: Filtering with WHERE, AND, OR, NOT, NULL