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

Differences and usage of WHERE and HAVING clauses in SQL

SQL (Structured Query Language) is a powerful tool for manipulating and retrieving database data. When using data aggregation functions such as COUNT() or SUM(), it is important to understand the difference between the HAVING and WHERE clauses.

Overview

The WHERE clause is used to filter rows before aggregation. It evaluates the condition against individual rows in the table.

HAVING clause is used to filter rows after aggregation. It evaluates conditions based on aggregated data.

Key differences

The most critical difference between HAVING and WHERE is the timing of their filtering:

• WHERE: Filter rows before aggregation.
• HAVING: Filter rows after aggregation.

Example

Consider the following example:

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

This query uses the WHERE clause to select all addresses located in the state "MA" before grouping. It then groups the results by city and counts the number of addresses in each city.

Now, let’s modify the query using the HAVING clause:

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

In this modified query, the HAVING clause is used to filter the results after aggregation. It only selects cities with more than 5 addresses.

When to use WHERE and HAVING

• WHERE: is great for filtering individual rows before aggregating them, for example based on a specific attribute or value.
• HAVING: is useful for filtering aggregated statistics, such as selecting groups based on minimum or maximum counts, averages, or other aggregated data.

By understanding the timing difference between WHERE and HAVING, developers can efficiently retrieve data from the database based on specific conditions before or after aggregation.

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