6

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?

davidmontoyago
  • 1,735
  • 13
  • 17
pooja
  • 73
  • 6
  • 6
    This is a MySQL extension to the standard. You can't reference column aliases in `having` in SQL Server for example. – Martin Smith Aug 23 '12 at 14:10

1 Answers1

5

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

davidmontoyago
  • 1,735
  • 13
  • 17
  • But how is it that 'having' picks up the alias even before 'select' is executed? – pooja Aug 23 '12 at 14:30
  • Any citation for "In standard SQL you can't use a HAVING clause without a GROUP BY"? – Martin Smith Aug 23 '12 at 14:34
  • No citation, but it's well known that HAVING was created for filtering rows resulting of a GROUP BY clause. http://en.wikipedia.org/wiki/SQL – davidmontoyago Aug 23 '12 at 14:40
  • @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. – davidmontoyago Aug 23 '12 at 14:43
  • 1
    `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](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) where you believe this is prohibited. All I see is ` ::= [ ][ ][ ]` indicating any permutation of the three is fine.
     – Martin Smith Aug 23 '12 at 14:45
  • 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) – pooja Aug 23 '12 at 16:17
  • @user1619966 the answer has been improved. – davidmontoyago Aug 23 '12 at 17:19
  • @davidmontoyago thank you , the last post(EDIT 2 regarding the ALIAS) was really helpful! – pooja Aug 23 '12 at 17:27
  • @davidmontoyago 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? – pooja Aug 23 '12 at 17:38