The HAVING Clause
The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause. Let’s look at some examples that illustrate this. The following query returns the number of orders per customer:
SELECT CUST_NBR, COUNT(ORDER_NBR)FROM CUST_ORDERGROUP BY CUST_NBRHAVING CUST_NBR < 260;CUST_NBR COUNT(ORDER_NBR) ---------- ---------------- 201 2 231 6 244 2 255 6
Notice that the output only includes customers with numbers below 260. That’s because the HAVING clause specified CUST_NBR < 260 as a condition. Orders for all customers were counted, but only those groups that matched the specified HAVING condition were returned as the result.
The previous example is a poor use of the HAVING clause, because that clause only references unsummarized data. It’s more efficient to use WHERE CUST_NBR < 260 instead of HAVING CUST_NBR < 260, because the WHERE clause eliminates rows prior to summarization, whereas HAVING eliminates groups post-summarization. A better version of the previous query would be:
SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER WHERE CUST_NBR < 260;
The next example shows a more appropriate use of the HAVING clause:
SELECT CUST_NBR, COUNT(ORDER_NBR)FROM CUST_ORDERGROUP BY CUST_NBRHAVING COUNT(ORDER_NBR) > 2;CUST_NBR ...