Wed 9 Nov 2005
Its a SQL Thing
Posted by Nola under Uncategorized
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?
November 11th, 2005 at 12:22 pm
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.
January 30th, 2006 at 12:59 pm
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.
January 30th, 2006 at 7:38 pm
Thanks, I’ll give that a try!