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

Detailed explanation of the differences between HAVING and WHERE clauses in SQL

In SQL, the HAVING and WHERE keywords are used to filter data, but they have different functions. Both are used for conditional selection, but their application depends on the presence or absence of the GROUP BY clause.

WHERE clause

The

WHERE clause is executed before the aggregation operation and selects specific rows based on their values. For example, the following query retrieves the names of all students older than 20:

<code class="language-sql">SELECT Name
FROM Students
WHERE Age > 20;</code>

HAVING clause

In contrast, the HAVING clause is executed after applying the aggregate function. It filters row groups based on aggregated values, which is useful in scenarios where you need to examine aggregated results.

For example, the following query retrieves the average student age in each city, then filters the results to only show cities where the average age is greater than 25:

<code class="language-sql">SELECT City, AVG(Age) AS AvgAge
FROM Students
GROUP BY City
HAVING AvgAge > 25;</code>

Key differences

The fundamental difference between

HAVING and WHERE is their execution time. WHERE filters rows before aggregation, while HAVING filters groups after aggregation. This means that HAVING is used to check conditions on aggregated values, while WHERE is used to check conditions on individual row values.

Example

Consider the following table with sample data:

City	Age
Boston	22
Boston	25
New York	28
New York	30
Los Angeles	23
Los Angeles	29

Queries using WHERE:

<code class="language-sql">SELECT City, COUNT(*) AS Count
FROM Students
WHERE Age > 25;</code>

Output:

City	Count
New York	2

Queries using HAVING:

<code class="language-sql">SELECT City, COUNT(*) AS Count
FROM Students
GROUP BY City
HAVING COUNT(*) > 1;</code>

Output:

City	Count
Boston	2
New York	2

In this example, the WHERE clause retrieves the number of students older than 25, while the HAVING clause retrieves the cities with more than 1 students in each city.

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