The difference between where and what is related to pseudonyms

Question

If I create an alias in the select clause, then I cannot use it in the where clause, because in accordance with the order of execution of sql queries, where preceded by select .

But I can create an alias in the select clause and use it in the having , although having preceded by select .

Why is this so?

Example:

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

this does not work. But,

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

It works. Why is that?

Answer 1

Mostly because they are defined for different purposes. The WHERE is for filtering records, and the HAVING is for filtering using aggregate functions ( GROUP BY ). The second query uses implicit GROUP BY filtering, so for example, if you add another column to the SELECT , you will get different results.

EDIT based on correction by Martin Smith

HAVING was created to allow row filtering as a result of GROUP BY . If GROUP BY not specified, the entire result is considered a group.

If neither <where clause> nor << 29> is specified, then let T be the result of the previous <from clause>

or

... a group is a whole table, unless specified <group by clause>

EDIT 2 Now about ALIAS:

The WHERE clause specification for column references in a search term says the following:

Each <column reference> directly contained in the <search condition> must uniquely refer to a column of T or an external link.

See: 7.6 <where clause> , Syntax Rule 1.

The HAVING clause specification for column references in a search term says the following:

Each <column reference> directly contained in the <search condition> must uniquely refer to a grouping column of T or be an external link.

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

And the grouping column is defined as:

The column <group by clause> contains the column indicated by the column.

So, in conclusion, WHERE should refer to the table column, and the HAVING should refer to the grouping column of the row group.

(Second draft informal review) ISO / IEC 9075: 1992, Database SQL - July 30, 1992