11/09/2005

Its a SQL Thing

I want to do something like this:

SELECT h.username AS user
FROM userdata AS h
WHERE user="nstowe";

I am using the ALIAS of h.username, in my WHERE.

I'm using MySQL.

It doesn't work, although I would of thought it would. Any ideas? is this same for Oracle, PG?

3 Comments:

Blogger Brian Ray said...

Hey PhpGirl,

What MySQL version. If greater than 4.1, you can use subselects. Totally untested, but something like:

SELECT *
   FROM (
      SELECT h.username user
      FROM userdata h
   )
WHERE user ="nstowe" ;

This of course is not effiecent so I am sure there is a better way. BTW, 'AS' is considered noise-- it's not needed.

12:22 PM  
Blogger Lucidix said...

This is going to be MySQL specific, and it has to do how MySQL internally handles WHERE vs. HAVING, which actually may have changed as of 5.0 (I remember reading something about planned changes for MySQL 5 some time ago, and it mentioned the behavior of HAVING, but TIAS)

I'll try to simplify it a bit:

Essentially any conditions in the WHERE clause are applied before the first pass over the data. At that point "user" doesn't exist yet.

HAVING is applied AFTER the table has been processed and WHERE has been applied, aliases have been created, etc.

You can write your query as:

SELECT
h.username AS user
FROM
userdata AS h
HAVING
user = 'nstowe';

But, and I'm not entirely sure as I haven't looked at the MySQL source code, I believe what's happening internally, again, is this:

First, WHERE clauses are taken into consideration when performing the first table scan. Since there are none, the whole table is read. (Potential performance issue with large tables?)

Then, HAVING is taken into consideration during the second pass and everything but "nstowe" is thrown out.

I haven't really run into any performance issues myself, so maybe I'm wrong on the internals, or it simply doesn't affect performance as much as I thought.

12:59 PM  
Blogger Nola said...

Thanks, I'll give that a try!

7:38 PM  

Post a Comment

Links to this post:

Create a Link

<< Home