What's the difference between HAVING and WHERE in MySQL Query?

Question

I have a view (viewX) based on joins of some tables:

When I use WHERE, the query is delayed, processor usage goes to 50% and finally I need to close mysqld.exe service and restart to try to solve the problem again.

When I use HAVING, the query executes perfectly and quickly, I get the results and all is ready.

The query is similar to this:

SELECT * FROM viewX WHERE column_of_view = 'foo'

SELECT * FROM viewX HAVING column_of_view = 'foo'

What is happening?

The solution I found is to do something like this:

SELECT * FROM (SELECT * FROM viewX) as T WHERE column_of_view = 'foo'

SELECT * FROM (SELECT * FROM viewX) as T HAVING column_of_view = 'foo'

BOTH QUERIES WORKS FINE, BUT, I think this is BAD! (SELECT * FROM (...viewX)????)

Answer 1

Having is used for aggregate functions like sum,avg so on and it can be using only in select statement.where clause won't work in aggregate conditions eg: where sum(mark) > 300 // its not true

Answer 2

WHERE is used while listing and no ALIAS names are available yet

HAVING filters rows after listing all possible rows so ALIAS names are generated

There should be a problem while filtering rows inline.

Answer 3

WHERE is for filtering query results based on condition.

HAVING is for applying a filter on results of an aggregate function. In absence of aggregate function it functions same as WHERE.

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

Answer 4

It depends on the definition of the view - having clause should only apply to an aggregate query and its applied after the grouping. Have you looked at the two query plans (with explain)?