select customer_id
from orders
group by customer_id
having count(*) > 4;
Question: What will be the result of executing the above query?
Answer: It will return all rows where COUNT(*) > 4 even though COUNT(*) is not part of the SELECT list.
As we know, whatever you want to have in HAVING clause, should have been listed in SELECT list. If you specify some column in HAVING clause, which wasn’t listed in SELECT clause, it generates error. But not in this case. Let me explain why.
Assume you have the following query :
select customer_id , COUNT(*)
from orders
group by customer_id
having count(*) > 4;
As you can see below this query is identical to the first query, the only difference is the second column, that was explicitly specified in the second query.
In the first query we did not specify COUNT(*), but it was specified by default implicitly. Therefore the first query did produce a result.
