The difference between where and having in mysql

The WHERE clause filters rows and is applied before grouping; the HAVING clause filters groups and is applied after grouping and can use the results of the group aggregate function.

The difference between WHERE and HAVING clauses

In MySQL, both WHERE and HAVING clauses are used for filtering datasets, but they differ primarily in their scope and how they are used.

WHERE clause

• acts in the FROM clause of the SELECT statement.
• Used to filter rows and can only be based on columns in the table.
• Applies before grouping rows.

HAVING clause

• acts after the GROUP BY clause of the SELECT statement.
• Used to filter groups, which can be based on the results of group aggregation functions.
• Apply after grouping rows.

Usage Example

WHERE Clause:

<code class="sql">SELECT * FROM customers WHERE age > 30;</code>

This query selects all age greater than 30 customers.

HAVING clause:

<code class="sql">SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING customer_count > 100;</code>

This query selects all cities with more than 100 customers from the customers table.

Summary

• The WHERE clause is used to filter rows and is applied before grouping.
• HAVING clause is used to filter groups, is applied after grouping, and can use the results of group aggregate functions.

The above is the detailed content of The difference between where and having in mysql. For more information, please follow other related articles on the PHP Chinese website!