Differences between the SQL HAVING and WHERE Clauses ?

We saw earlier that a WHERE clause filter won't work with an aggregate function column. However, the HAVING clause will filter a regular, or non-aggregate column. To do this, we must include the GROUP BY clause. Have a look at this query:

USE     packt_online_shop; 

SELECT  PC.ProductCategoryID, PC.ProductCategoryName,  

        AVG(P.UnitKGWeight) AS 'AVERAGE PRODUCT KG WEIGHT',  
MIN(P.NetRetailPrice) AS 'MINIMUM NET RETAIL PRICE' 

 Products P ON PC.ProductCategoryID = P.ProductCategoryID 
FROM    ProductCategories PC INNER JOIN 

HAVING PC.ProductCategoryID = 2; 

It did not work because we forgot the GROUP BY clause. If we add the GROUP BY clause, it will work:

USE      packt_online_shop; 

SELECT   PC.ProductCategoryID, PC.ProductCategoryName,  

         AVG(P.UnitKGWeight) AS 'AVERAGE PRODUCT KG WEIGHT',  
MIN(P.NetRetailPrice) AS 'MINIMUM NET RETAIL PRICE' 

         Products P ON PC.ProductCategoryID = P.ProductCategoryID 
FROM     ProductCategories PC INNER JOIN 
GROUP BY PC.ProductCategoryID, PC.ProductCategoryName 

HAVING   PC.ProductCategoryID = 2; 

As we can see, the query worked.

If we set up the GROUP BY clause correctly, as explained previously, we can use both WHERE and HAVING clauses in the same query. This query combines WHERE and HAVING clauses in one query:

USE      packt_online_shop; 

SELECT   OI.OrderID, P.ProductName,  

         SUM((OI.UnitPrice - OI.Discount) * OI.Quantity) AS  
'TOTAL REVENUE PER PRODUCT PER ORDER' 

  Products P ON OI.ProductID = P.ProductID 
FROM     OrderItems OI INNER JOIN 
WHERE    OI.OrderID = 5 

HAVING   SUM((OI.UnitPrice - OI.Discount) * OI.Quantity) > 4.50 
GROUP BY OI.OrderID, P.ProductName 

ORDER BY 'TOTAL REVENUE PER PRODUCT PER ORDER' ASC; 

The line 5 SUM aggregate function requires the line 8 GROUP BY clause, and the line 9 HAVING clause filters on the SUM function values. The line 7 WHERE clause filters the OrderItems.OrderID values. As explained earlier, we could have placed all the filters in the HAVING clause, combining them with the AND keyword: 

Look at the HAVING clause on line 9:

USE packt_online_shop;

SELECT OI.OrderID, P.ProductName,

SUM((OI.UnitPrice - OI.Discount) * OI.Quantity) AS

'TOTAL REVENUE PER PRODUCT PER ORDER'

Products P ON OI.ProductID = P.ProductID

FROM OrderItems OI INNER JOIN WHERE OI.OrderID = 5

HAVING (SUM((OI.UnitPrice - OI.Discount) * OI.Quantity) > 4.50)

GROUP BY OI.OrderID, P.ProductName AND (OI.OrderID = 5)

ORDER BY 'TOTAL REVENUE PER PRODUCT PER ORDER' DESC

However, we want as much filtering as possible to happen in the WHERE clause and as little as possible in the HAVING clause. As MySQL runs a query, it evaluates the WHERE clause early on. This eliminates as many rows as possible as early as possible. MySQL then runs the GROUP BY clause if it sees one, and then runs the HAVING clause if it sees one. If a query eliminates as many rows as possible with the WHERE clause, MySQL can avoid operations on fewer rows. That way, it will use fewer resources and the query will have better performance.