How does the HAVING clause really work?

Question

We can use the HAVING clause in the SQL query to filter row groups. When we use the GROUP BY clause, it works that way.

But look at this query:

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

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

If HAVING does perform group filtering, after WHERE we don't have a single row, so we don’t have any group, and the result should be “No row selection”.

But in PostgreSQL, MySQL and Oracle we get "1" as a result of the query.

Question: How does HAVING work?

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

Answer 1

If there is no GROUP BY , the aggregate always returns a string, in your case COUNT(*) returns 0 .

This column is not in your selection list, but a hard-coded literal 1

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

See fiddle

Answer 2

HAVING without GROUP BY cluase is valid and works on the whole table. From the SQL 92 standard:

7.10

:: = HAVING

Syntax Rules

1) Let HC -. Let TE be the one that immediately contains HC.

If TE does not immediately contain a, then GROUP BY () is implicit.

and

:: = GROUP BY

 <grouping specification> ::= <grouping column reference> | <rollup list> | <cube list> | <grouping sets list> | <grand total> | <concatenated grouping> <grouping set> ::= <ordinary grouping set> | <rollup list> | <cube list> | <grand total> <grand total> ::= <left paren> <right paren> 

As you can see, GROUP BY () considered as grand total .

In your example, you have:

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

actually something like:

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