Mysql – Having

As per SQL standard –

The HAVING clause without a GROUP BY clause acts like the WHERE clause. If the HAVING clause contains no aggregate functions, use the WHERE clause for faster performance.

here’s an example of a SQL statement where we use HAVING:

select column1 from table1
where condition1
having condition2;

isn’t it the same exact thing if we do this:

select column1 from table1
where condition1 AND condition2;

what is the difference between these two?

In your example, they should do the same thing. But WHERE gets processed before any GROUP BY, and so it doesn’t have access to aggregated values (that is, the results of Min()Max(), etc. functions). HAVING gets processed after GROUP BY and so can be used to constrain the result set to only those with aggregated values that match a certain predicate.

The where clause applies to all rows in the result set. The having clause is applied to the groups created by a group by clause. So if your group consists of column1 in your example and the conditions were on column 1, then the where is the same as the having, since a row is the same as the “group”

HAVING is for use with aggregates, e.g., HAVING SUM(column1) > 200WHERE is just for the columns, e.g., WHERE column1 < 20.

HAVING is applied after GROUP BY which is applied after WHERE.

in SQL the WHERE clause is evaluated before the SELECT clause, therefore the result of a set function is ‘out of scope’ in the WHERE clause, that’s why we use Having.

HAVING specifies a search condition for a group or an aggregate function used in a SELECT statement.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s