How does the HAVING clause really work?

Question

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

Scram

Silly way to detect if a table has 0 rows or not... – Formation

WHERE return 0 rows, so we have 0 groups of rows. If HAVING do only row groups filtration, why is start and do some evaluation for empty input? – Scram

Answer 1

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

Rajab

Ok, if HAVING without GROUP BY always returns a row what data this row contain? – Scram

@potapuff: If there's no matching row COUNT returns zero, SUM, MIN, etc. NULL. – Rajab

Answer 2

HAVING without GROUP BY cluase is valid and operates on entire table. From SQL Standard 92:

7.10

::= HAVING

Syntax Rules

1) Let HC be the . Let TE be the 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 see GROUP BY () is treated as grand total.

In your example you have:

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

is actually something like:

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

Eran

By the way, most of databases (MySQL, PostgreSQL, Oracle) doesn't support GROUP BY () :) – Scram