potapuff potapuff - 3 years ago 169
MySQL Question

How does the HAVING clause really work?

We able to use HAVING clause in SQL-query to filtrate groups of row. When we use GROUP BY clause it work directly in this way.

But, let's look to this query:

select 1 where 1!=1 having count(*)=0;

(or append it with 'from dual' for Oracle).

If HAVING really do group filtration, after WHERE we have no any rows, so we have no any group and result must be 'No row selected'.

But in PostgreSQL, MySQL and Oracle we get '1' as result of query.

Question: how does HAVING really work?

SQL Fiddle for test: http://www.sqlfiddle.com/#!15/d5407/51

Answer Source

If there's no GROUP BY an aggregate always returns a row, in your case the COUNT(*) returns 0.

This column is not in your Select list, but the hard-coded literal 1

select count(*) where 1!=1 ;
select 'bla' where 1!=1 having count(*)=0;

See fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download