WHERE clause
The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
Overview
The WHERE clause restricts which rows a query returns by applying a boolean condition to each row before any grouping or aggregation happens. It is one of the most fundamental pieces of SQL: nearly every non-trivial SELECT, UPDATE, or DELETE statement includes one to narrow down a dataset.
WHERE operates on individual rows, evaluating the condition once per row coming out of the FROM clause (including any JOINs). Rows where the condition evaluates to TRUE are kept; rows where it evaluates to FALSE or NULL (unknown) are discarded.
Syntax and operators
A WHERE condition can combine comparison operators (=, <>, <, >, <=, >=), logical operators (AND, OR, NOT), and predicates like IN, BETWEEN, LIKE, IS NULL, and EXISTS:
Copy code
SELECT order_id, customer_id, order_total
FROM orders
WHERE status = 'completed'
AND order_total > 100
AND order_date >= DATE '2026-01-01';
Where it fits in query execution
Conceptually, SQL evaluates clauses in this order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. Because WHERE runs before grouping, it cannot reference aggregate functions such as SUM() or COUNT() -- use HAVING for that instead.
DuckDB relaxes one common annoyance here: a non-aggregate column alias defined in the SELECT list can be referenced directly in the WHERE clause of the same query, without wrapping the query in a subquery or CTE:
Copy code
SELECT order_total * 1.08 AS total_with_tax
FROM orders
WHERE total_with_tax > 100;
DuckDB also supports the SELECT-less FROM tbl WHERE ... shorthand for quick, ad hoc filtering during exploration.
Common patterns
WHERE clauses frequently use subqueries (WHERE customer_id IN (SELECT ...)), correlated conditions (WHERE EXISTS (SELECT 1 FROM ... WHERE ...)), and range checks (WHERE amount BETWEEN 10 AND 100).
Related terms
The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().
GROUP BY clause →GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.
DELETE statement →The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
OVER clause →The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
FILTER clause →The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.
ORDER BY clause →The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.
FAQS
WHERE filters individual rows before grouping and cannot reference aggregate functions; HAVING filters groups after GROUP BY and is designed to work with aggregates like SUM() or COUNT().
Not directly -- aggregates aren't computed yet when WHERE runs. To filter on an aggregate, use HAVING or filter in a subquery/CTE that already computed the aggregate.
WHERE runs after the FROM clause and any JOINs have produced their combined row set, but before GROUP BY, so it can reference columns from any joined table.
