HAVING vs. WHERE in SQL: When to Use Each Clause for Filtering?

Differences and applications of HAVING and WHERE clauses in SQL

There is a key difference between the HAVING and WHERE clauses when using aggregate functions and SQL queries. Let’s dive into their differences to improve your SQL skills.

HAVING: filtering after aggregation

HAVING is used to apply a condition on an aggregated value after the aggregation operation is complete. In other words, it acts as a post-aggregation filter, checking whether the aggregation result satisfies certain conditions.

For example, the following query retrieves cities in Massachusetts (MA) and their corresponding number of addresses:

<code class="language-sql">SELECT City, COUNT(1) AS CNT
FROM Address
WHERE State = 'MA'
GROUP BY City;</code>

To further refine the results and only show cities with more than five addresses, use the HAVING clause:

<code class="language-sql">SELECT City, COUNT(1) AS CNT
FROM Address
WHERE State = 'MA'
GROUP BY City
HAVING CNT > 5;</code>

WHERE: Filter before aggregation

Unlike HAVING, WHERE is used for filtering before aggregation. It evaluates conditions before the aggregation process. This means that only records that satisfy the specified WHERE condition will be included in the aggregation.

Continuing with the previous example, you can use the WHERE clause to limit the selection to cities in Massachusetts before calculating addresses:

<code class="language-sql">SELECT City, COUNT(1) AS CNT
FROM Address
WHERE State = 'MA' AND City = 'Boston'
GROUP BY City;</code>

Comparison of key features

特性	HAVING	WHERE
时间	聚合之后	聚合之前
目的	聚合后过滤	聚合前过滤
作用范围	应用于聚合值	应用于单个记录

Summary

HAVING and WHERE both play a vital role in SQL query optimization. By understanding their different functions and execution times, you can effectively refine your results and enhance your data analysis capabilities.

The above is the detailed content of HAVING vs. WHERE in SQL: When to Use Each Clause for Filtering?. For more information, please follow other related articles on the PHP Chinese website!