Breaking

Friday, May 1, 2020

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.

No comments:

Post a Comment