SQL WHERE: filter rows with AND, OR, and NULLs

The SQL WHERE clause is an SQL clause that filters table rows by condition. In a SQL query WHERE clause, rows match only when the predicate evaluates to TRUE—both FALSE and NULL/UNKNOWN results are excluded. This three-valued logic is the most common source of unexpected WHERE behavior around NULLs.

SQL WHERE Clause Example For Row Filtering

SQL Editor

CREATE TABLE products (name TEXT, category TEXT, price REAL);
INSERT INTO products VALUES
  ('Keyboard', 'Electronics', 49.99),
  ('Mouse', 'Electronics', 25.00),
  ('Notebook', 'Office', 4.50),
  ('Monitor', 'Electronics', 299.00);
SELECT name, category, price
FROM products
WHERE category = 'Electronics' AND price < 100;

Output:

Output will appear here...

Output:

+----------+-------------+-------+
| name     | category    | price |
+----------+-------------+-------+
| Keyboard | Electronics | 49.99 |
| Mouse    | Electronics | 25.0  |
+----------+-------------+-------+
2 row(s)

How This Example Works

CREATE TABLE and INSERT build a products table with four rows across two categories.
The SQL query WHERE clause category = 'Electronics' AND price < 100 combines two conditions with AND—both must be TRUE for a row to appear.
“Monitor” is in Electronics but costs 299.00, so the price condition eliminates it. “Notebook” fails the category check. Only “Keyboard” and “Mouse” satisfy both predicates.

What Is a SQL WHERE Clause?

A SQL WHERE clause is a boolean filter applied to each row individually. The database evaluates the expression for every candidate row and keeps only those where the result is TRUE. Any expression that returns FALSE or NULL is discarded. WHERE operates before GROUP BY and aggregate functions, which distinguishes it from HAVING.

Common Mistakes in SQL WHERE Clauses

Mistake: Comparing a column to NULL with =.

Wrong:

WHERE category = NULL

Right:

WHERE category IS NULL

Why: = NULL evaluates to UNKNOWN, and WHERE filters out UNKNOWN rows. Use IS NULL or IS NOT NULL.

Mistake: Mixing AND and OR without parentheses.

Wrong:

WHERE category = 'Office' OR category = 'Electronics' AND price < 50

Right:

WHERE (category = 'Office' OR category = 'Electronics') AND price < 50

Why: AND binds tighter than OR, so parentheses are required to match your intent.

WHERE vs HAVING

WHERE	HAVING
Filters individual rows before grouping	Filters groups after aggregation
Cannot reference aggregate functions	Can reference `COUNT`, `SUM`, etc.
Reduces rows fed into `GROUP BY`	Reduces groups in the final result

Rule: use WHERE to exclude rows before aggregation, HAVING to exclude groups after it. Filtering early with WHERE reduces the work the aggregation step does.

Performance Considerations

WHERE predicates on indexed columns turn full table scans into index lookups. Wrapping the column in a function—LOWER(name), DATE(created_at)—prevents the optimizer from using the index; transform the comparison value instead. For AND-connected conditions, a composite index matching the predicate columns in order gives the best performance. OR conditions often result in separate index scans merged together, which is slower than AND on the same index.

Security Notes

Build WHERE conditions with parameterized queries, never by concatenating user input into SQL strings. For dynamic column names, validate against a strict allow-list. For IN (...) lists, generate placeholders and bind each value. Always preview UPDATE and DELETE statements with a SELECT using the same WHERE clause before executing the write.

FAQ

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before GROUP BY; HAVING filters aggregated groups after it. WHERE cannot reference aggregate functions like COUNT() or SUM(). A query can use both: WHERE narrows the input rows, then HAVING narrows the grouped output.

Why does `= NULL` not work in a WHERE clause?

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL returns UNKNOWN, and WHERE discards UNKNOWN rows. Use IS NULL to test for NULL values. Some databases also support IS NOT DISTINCT FROM for NULL-safe equality comparisons.

Does WHERE clause order affect performance?

The SQL standard does not guarantee evaluation order, and most optimizers reorder predicates internally. Write conditions in the order that reads best. Performance depends on which columns are indexed and how selective each predicate is, not on the order you write them.