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

The difference between HAVING and WHERE clauses in SQL SELECT statement

In a SQL SELECT statement, the HAVING and WHERE clauses serve different purposes in data filtering. Understanding their usage is critical to obtaining accurate query results.

HAVING clause

HAVING clause is applied after aggregation using GROUP BY clause. It filters grouped rows based on a condition applied to the aggregated value. For example:

SELECT City, COUNT(1) AS CNT
FROM Address
WHERE State = 'MA'
GROUP BY City
HAVING COUNT(1) > 5

This query returns a table containing cities in Massachusetts and their number of addresses (more than 5).

WHERE clause

In contrast, the WHERE clause filters rows before aggregation. It evaluates the condition on individual rows and selects only the rows that satisfy the condition. For example:

SELECT City, COUNT(1) AS CNT
FROM Address
WHERE State = 'MA' AND City LIKE '%ton'
GROUP BY City

This query returns a table containing the number of cities in Massachusetts (with names ending in "ton") and their addresses.

Key differences

The main difference between HAVING and WHERE is when they are applied:

• HAVING filters aggregated data after grouping.
• WHERE Filter individual rows before grouping.

By leveraging this distinction, developers can precisely manipulate and filter data in SQL SELECT statements to retrieve the desired results.

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