difference between where and having with respect t

Question

If I create an alias in the select clause then I cannot use it in the where clause because according to the order of execution of sql queries where comes before select.

But I can create an alias in the select clause and use it in a having clause though having comes before select.

Why is it so?

Ex:

select type, (case when number>25 then 1 else 0 end) inc 
from animals
where inc='1';

this wont work. But,

select type, (case when number>25 then 1 else 0 end) inc 
from animals
having inc='1'; 

This works. Why so?

Answer 1

Basically because they where defined for different purposes. The WHERE clause is for records filtering and the HAVING clause is designed for filtering with aggregate functions (GROUP BY). In your second query an implicit GROUP BY filtering is being used, so for instance, if you add another column to the SELECT clause you will end up with different results.

EDIT based on correction by Martin Smith

HAVING was created to allow filtering of rows resulting of a GROUP BY. When no GROUP BY is specified, the whole result is considered a group.

If neither a <where clause> nor a <group by clause> is specified, then let T be the result of the preceding <from clause>

or

...the group is the entire table if no <group by clause> is specified

EDIT 2 Now regarding the ALIAS:

The specification for the WHERE clause regarding the columns references in the search condition says this:

Each <column reference> directly contained in the <search condition> shall unambiguously reference a column of T or be an outer reference.

Refer to: 7.6 <where clause>, Syntax Rule 1.

The specification for the HAVING clause regarding the columns references in the search condition says this:

Each <column reference> directly contained in the <search condition> shall unambiguously reference a grouping column of T or be an outer reference.

Refer to: 7.8 <having clause>, Syntax Rule 1.

And a grouping column is defined as:

A column referenced in a <group by clause> is a grouping column.

So in conclusion the WHERE must reference a column of the table and the HAVING clause must reference a grouping column of the group of rows.

(Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992