Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server AS statement aliased column within WHERE statement

I want to execute a query in which I rename one of the columns using the 'AS' statement and reuse that aliased column name within the 'WHERE' statement. Below is an example:

SELECT lat AS latitude  FROM poi_table  WHERE latitude < 500 

The problem here is that SQL Server does not like this query because of the WHERE clause and the AS statement name being referenced in the WHERE clause. Can anyone explain why this is happening and what I can do to remedy my situation?

Suppose I were to have a formula that I have aliased in the SELECT portion of the query, how do I tackle that?

SELECT *,  ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) )  AS distance FROM poi_table  WHERE distance < 500; 
like image 534
GobiasKoffi Avatar asked Jun 22 '10 19:06

GobiasKoffi


People also ask

Can we use column alias in WHERE clause SQL Server?

In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.

Can you reference alias in WHERE clause?

Standard SQL disallows references to column aliases in a WHERE clause.

How can I alias column name in SQL Server?

To create a column aliasThe alias must follow all naming conventions for SQL. If the alias name you enter contains spaces, the Query and View Designer automatically puts delimiters around it.

WHERE can you use aliases in SQL?

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.


2 Answers

SQL doesn't typically allow you to reference column aliases in WHERE, GROUP BY or HAVING clauses. MySQL does support referencing column aliases in the GROUP BY and HAVING, but I stress that it will cause problems when porting such queries to other databases.

When in doubt, use the actual column name:

SELECT t.lat AS latitude    FROM poi_table t  WHERE t.lat < 500 

I added a table alias to make it easier to see what is an actual column vs alias.

Update


A computed column, like the one you see here:

SELECT *,         ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance   FROM poi_table   WHERE distance < 500; 

...doesn't change that you can not reference a column alias in the WHERE clause. For that query to work, you'd have to use:

SELECT *,         ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance   FROM poi_table  WHERE ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) < 500; 

Be aware that using a function on a column (IE: RADIANS(lat)) will render an index useless, if one exists on the column.

like image 198
OMG Ponies Avatar answered Sep 23 '22 05:09

OMG Ponies


SQL Server is tuned to apply the filters before it applies aliases (because that usually produces faster results). You could do a nested select statement. Example:

SELECT Latitude FROM  (     SELECT Lat AS Latitude FROM poi_table ) A WHERE Latitude < 500 

I realize this may not be what you are looking for, because it makes your queries much more wordy. A more succinct approach would be to make a view that wraps your underlying table:

CREATE VIEW vPoi_Table AS  SELECT Lat AS Latitude FROM poi_table 

Then you could say:

SELECT Latitude FROM vPoi_Table WHERE Latitude < 500 
like image 43
tgolisch Avatar answered Sep 22 '22 05:09

tgolisch