I’m reading a paper called Query Optimization Techniques - Tips For Writing Efficient And Faster SQL Queries.

That document suggests that the HAVING clause is “useless” in a SELECT statement:

Tip #2: Avoid including a HAVING clause in SELECT statements

The HAVING clause is used to filter the rows after all the rows are selected and it is used like a filter. It is quite useless in a SELECT statement. It works by going through the final result table of the query parsing out the rows that don’t meet the HAVING condition.

Example:

Original query:

SELECT s.cust_id,count(s.cust_id)
FROM SH.sales s
GROUP BY s.cust_id
HAVING s.cust_id != '1660' AND s.cust_id != '2';

Improved query:

SELECT s.cust_id,count(cust_id)
FROM SH.sales s
WHERE s.cust_id != '1660'
AND s.cust_id !='2'
GROUP BY s.cust_id;

Question:

Is that assertion correct? Does the HAVING clause not have a purpose in a SELECT statement?

CodePudding user response：

If it were useless, it wouldn't exist.

Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE

Based on your example, you'd use it as e.g.

SELECT s.cust_id,count(cust_id)
FROM SH.sales s
WHERE s.cust_id != '1660'
AND s.cust_id !='2'
GROUP BY s.cust_id
HAVING count(cust_id) > 5;       --> here

What is its purpose? You can't use WHERE along with aggregate functions, e.g. this is invalid:

FROM ...
WHERE count(cust_id) > 5    --> this
AND ...