What is the difference between WHERE and HAVING clauses in SQL?

The WHERE and HAVING clauses both filter data in SQL, but they operate at different stages of query execution and on different types of data.

WHERE filters individual rows before any grouping or aggregation takes place. It cannot reference aggregate functions. In contrast, HAVING filters groups of rows after they have been grouped and aggregated, and it is specifically used to filter based on the results of aggregate functions.

Mechanics and Why it Matters

SQL queries generally follow a logical processing order: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

WHERE Clause: Applied early in the process, it evaluates conditions on individual rows produced by FROM and JOIN clauses. This means WHERE predicates operate on the raw, unaggregated column values. Since aggregation hasn't occurred yet, you cannot use functions like SUM(), COUNT(), or AVG() in a WHERE clause.

HAVING Clause: Applied later, after GROUP BY has formed groups and aggregate functions have computed values for each group. HAVING then filters these resulting groups based on conditions that often involve aggregate function outputs.

Performance and Scalability

Understanding this distinction is crucial for query performance and cost efficiency, especially in data engineering contexts. Early Filtering with WHERE: Filtering rows with WHERE reduces the dataset size before* potentially expensive operations like GROUP BY and aggregation. This minimizes the amount of data that needs to be processed, shuffled (e.g., in Spark), or scanned (e.g., from Snowflake micro-partitions). Fewer rows mean less I/O, less memory usage, and faster computation, directly impacting query execution time and cloud compute costs. * Late Filtering with HAVING: HAVING operates on the (potentially much smaller) set of groups. While necessary for aggregate conditions, if a condition could have been applied earlier with WHERE, applying it with HAVING means the aggregation engine still had to process all the initial rows to form the groups, which can be inefficient. Always prefer WHERE when possible to reduce data volume early.

Example

SELECT
    customer_id,
    SUM(order_total) AS total_spent,
    COUNT(order_id) AS num_orders
FROM
    orders
WHERE
    order_date >= '2023-01-01' -- Filters individual orders before grouping
GROUP BY
    customer_id
HAVING
    SUM(order_total) > 1000 AND COUNT(order_id) > 5; -- Filters groups based on aggregates

In the interview, also mention that misplacing predicates can lead to incorrect results or significantly degrade performance, making this a fundamental concept for optimizing data pipelines.