CODEWITHSUNDEEP
sqloracle-databasequery-optimizationhaving
User1974
User1974

Reputation: 386

Is the HAVING clause “useless” in a SELECT statement?

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?

Upvotes: 0

Views: 974

Answers (1)

Littlefoot
Littlefoot

Reputation: 142753

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 ...

Upvotes: 3

Related Questions