difference between where and having with respect to aliases

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?

Rightist

This is a MySQL extension to the standard. You can't reference column aliases in having in SQL Server for example. – Hagiocracy

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

Marnimarnia

But how is it that 'having' picks up the alias even before 'select' is executed? – Rightist

Any citation for "In standard SQL you can't use a HAVING clause without a GROUP BY"? – Hagiocracy

No citation, but it's well known that HAVING was created for filtering rows resulting of a GROUP BY clause. en.wikipedia.org/wiki/SQL – Marnimarnia

@user1619966 HAVING doesn't pick up the alias before the select is executed. Every time you run an SQL query in most databases, the DB engine process your SQL and creates an execution plan, the SQL gets transformed and improved and what you see in your screen is not always exactly what gets executed. – Marnimarnia

SELECT SUM(number) FROM SomeTable is perfectly valid SQL. Pretty sure SELECT SUM(number) FROM SomeTable HAVING SUM(number) > 1000 is too and your last line is incorrect. Feel free to point me to the relevant part of the specification where you believe this is prohibited. All I see is <table expression> ::= <from clause> [ <where clause> ][ <group by clause> ][ <having clause> ] indicating any permutation of the three is fine. – Hagiocracy

I m sorry but I m still not clear. Let me put it this way. If we create an alias in the 'select' statement we cannot use it in the 'where' clause. If we want to use it then it should be something like this: select t1.type, t1.inc from (select type,(case when number>25 then 1 else 0 end)inc from animals) t1 where t1.inc=1; But with 'having' we can use it. why so? (My question is more toward using aliases) – Rightist

@user1619966 the answer has been improved. – Marnimarnia

@Marnimarnia thank you , the last post(EDIT 2 regarding the ALIAS) was really helpful! – Rightist

@Marnimarnia Could you please give one more clarification...In the example mentioned at the very beginning, since there is no 'group by' used with 'having', it considers the whole table as group and thereby it is able to understand the alias?..Is this a correct line of thought? – Rightist