Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 30
  AND stock > 0;

Try it

SQL Combining conditions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL NULL needs IS NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
  • LIKE is case-insensitive for ASCII in SQLite, case-sensitive in PostgreSQL (use ILIKE), 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 as WHERE is_active = 1.
  • Standard <>: != also works in all five dialects covered here.
SQL
-- PostgreSQL: case-insensitive pattern
SELECT name FROM products WHERE name ILIKE '%speaker%';

Usage notes

  • Three-valued logic. A comparison with NULL yields unknown, and WHERE discards unknown rows. x <> 1 does not return rows where x is NULL. See NULL and three-valued logic.
  • Precedence. AND binds tighter than OR. Use parentheses whenever you mix them.
  • Do not wrap indexed columns in functions. WHERE lower(email) = '[email protected]' cannot use a plain index on email; see sargable predicates.
  • Select aliases are not visible. SELECT price * 2 AS double_price ... WHERE double_price > 50 fails in standard SQL; repeat the expression or use a subquery.
  • Strings use single quotes. Double quotes mean identifiers in standard SQL.
  • UPDATE and DELETE use the same WHERE. Forgetting it affects every row, so write the WHERE first.