CODEWITHSUNDEEP
mysqlsqloracle-databasepostgresqlhaving-clause
potapuff
potapuff

Reputation: 1990

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

Upvotes: 5

Views: 271

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176104

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;

Upvotes: 3

dnoeth
dnoeth

Reputation: 60492

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

Upvotes: 7

Related Questions