Hello Readers,

1. During processing WHERE is used to SELECT data in the original tables while HAVING is used to filter the data in the result set that was produced by the query

2. WHERE and HAVING clause are used together in a SELECT query and also used with aggregate function namely SUM(), COUNT(), AVG() etc,  WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.

3. WHERE is applied before GroupBy while HAVING is applied after GroupBy.

4. WHERE clause can be used without GroupBy but HAVING is not used without GroupBy clause.

5. Apart from SELECT queries we can use the WHERE clause with UPDATE as well as DELETE but HAVING clause can only be used with SELECT query.

For Example:

We take the example of UPDATE query in MYSQL:

update CUSTOMERS set CUST_NAME="MAYANK" WHERE CUST_ID=1 ;  // works fine

update CUSTOMERS set CUST_NAME="MAYANK" HAVING CUST_ID=1 ; // error

Incorrect syntax near the keyword 'HAVING': update CUSTOMERS set CUST_NAME="MAYANK" HAVING CUST_ID=1

6. Now we take the examples of WHERE and HAVING clause in MySql. Assume that we run the query that find out the number of customers that we have city (India, china). So, the query may be written in one of the two ways which are given below:

7. option 1

SELECT City, COUNT(*) FROM Customers WHERE City IN (India,'china') GROUP BY City

option 2

SELECT City, COUNT(*) FROM Customers GROUP BY City HAVING City IN ('India','china')

In the first option we used the WHERE clause condition to filter the customers and the second query used to HAVING clause to apply to filter both the queries produced the same result, but the performance of both the clause not the same.

Above, the WHERE clause is evaluated before GroupBy and HAVING. And,Hence in first case the query processor will first filter the customers of India and china and then apply GroupBy on the filtered records to find the Count().

But in second query GroupBy applied on all the rows in the table and then HAVING is used to extract only the data of India and china. So, according of this second query doing quite a lot of unwanted work. So it take more time to processes as compared to first query in MYSQL.