I have learned to use HAVING when I use GROUP BY instead of the WHERE clause and never encountered any problems with it. Today I saw this on a w3schools.com a SQL Learning Page:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

1. Why this should work?
2. When I should use this?

CodePudding user response：

The difference between WHERE and HAVING is central to when you should employ one vs the other, or even both in conjunction.

HAVING enables you to filer your result set based on an aggregate value. In your example, you are limiting the data returned by the query to just those rows that have an aggregate (COUNT) greater than 25. It is not possible to define this sort of predicate in a WHERE due to syntax constraints.

The WHERE keyword is used to define predicates for the query based on discrete (non-aggregate) values. In your example, the WHERE is limiting the result based on the LastName and FirstName values.

They are able to be used in conjunction with one another as valid syntax because they perform different (albeit very similar) operations that cannot be accomplished by the other in isolation. As for why one should use this, it's simply a matter of needing to limit a result set based on both aggregate and discrete conditions.