Menu
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)????)
946
WHERE Clause is used to filter the records from the table based on the specified condition. HAVING Clause is used to filter record from the groups based on the specified condition.
A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause.
When to use WHERE and HAVING clauses? Though both are used to exclude rows from the result set, you should use the WHERE clause to filter rows before grouping and use the HAVING clause to filter rows after grouping.
Well, the main distinction between the two clauses is that HAVING can be applied for subsets of aggregated groups, while in the WHERE block, this is forbidden. In simpler words, after HAVING, we can have a condition with an aggregate function, while WHERE cannot use aggregate functions within its conditions.
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/
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
