Yeah, I've asked this question myself a million times.  It'd be very
useful and maybe some DB system knows how to do this, but for the most
part you just have to deal with this reality.

I believe that it's a matter of timing, really.  You can't use the alias
in the WHERE clause because it hasn't been created yet.

I believe everything is evaluated and executed then when it comes to the
final output, it uses the aliases as column headers (if no alias is set,
it'll use the column name... Unless it's an aggregate function, then it
uses nothing.. Which doesn't work well when you do a sub-select as a
table.. But that's another story).

What you COULD do, although it can add a little (or a lot) of overhead
to your query:

        SELECT total FROM (SELECT (sales * cost) as total FROM
sometable) t1 where > 100

There may be some cases where you'd want to do this, but be aware that
it most likely executes like this:

1. Performs the SELECT (sales * cost) query on ALL rows in the
database.. If you have a million rows and only 10 of them have sales *
cost > 100, it's going to be a major drag on your system to get those 10
rows.   It MAY have perform the function on all rows anyway since it's a
math function.   So this is "maybe" a drain if you do it as "WHERE
(sales * cost) > 100" but it's definitely a drain if you do it with the
sub-select I used above.   Also, not sure, but if you're using MySQL,
I'm not sure that it supports it below version 4.1 (or was that another
database I worked with recently?  I forget).

2. After performing the "total" on everything, then it evaluates the
"SELECT total... WHERE > 100" on the result set.  It treats the
sub-select as if it were a table, using it's results to select from.

Some just DB trivia for ya.  If you figure out a way to use an alias in
your WHERE clause without doing a sub-select, let me know. :)


> -----Original Message-----
> From: Ross Honniball [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, December 01, 2004 4:54 AM
> To: php DB
> Subject: [PHP-DB] Use of 'as' name in where sub-clause
> Hi All,
> In MySql, you CAN'T do the following:
> SELECT sales * cost AS total WHERE total > 100
> Instead, you need to say:
> SELECT sales * cost AS total WHERE sales * cost > 100
> I'm just curious WHY you can't do it. You can, for example, 
> specify ORDER 
> BY total.
> I can't think of any reason why it would be either difficult 
> or ambiguous 
> for the SQL engine to allow you to use your calculated field 
> name in the 
> WHERE part of the query.
> If someone can give me a good reason why this is so, it will stop it 
> annoying me so much.
> Regards ... Ross
> .
> . Ross Honniball. JCU Bookshop Cairns, Qld, Australia.
> .
> -- 
> PHP Database Mailing List (
> To unsubscribe, visit:

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to