MySQL Alias Question

Question

i am wondering why this fails

mysql> SELECT Continent C, Name, SurfaceArea
    -> FROM Country
    -> WHERE SurfaceArea = (
    -> SELECT MAX(SurfaceArea)
    -> FROM Country
    -> WHERE Continent = C);
ERROR 1054 (42S22): Unknown column 'C' in 'where clause'

its a answer provided by the certification guide for some sample exercises.

btw, for alias when do i have to use AS? isit optional?

Answer 1

In order to execute a correlated subquery, you need an alias for the outer table. You created an alias for the outer table's field. Take a look at the corrected code below, that has an alias for the table (Cou) that is referenced in the subquery (note that the field alias is not required so I removed it. There's no harm in adding it back if you wish):

SELECT Continent, Name, SurfaceArea
FROM Country Cou
WHERE SurfaceArea = 
(
    SELECT MAX(SurfaceArea)
    FROM Country
    WHERE Continent = Cou.Continent
);

Regarding the usage of AS, it's optional. For example, in the query above you could write Country AS Cou, it would be the same.

Answer 2

I'm assuming it has something to do with you're MySQL version. I just tested exactly the same query and it succeeds (syntaxically at least). Thats with MySQL 5.0.45.

If the MySQL version is not the issue, you could also try retyping the query. It may sound silly but sometimes unprintable characters may slip in your query while typing or copy/pasting (I'm thinking copy/paste from a PDF here, especially, since it's from a guide).

And, yes, AS is optional for aliases.