Can I Use Aliases in SQL WHERE Clauses?

Referencing Aliases in WHERE Clauses

In SQL, the execution order of statements within a query is crucial. In particular, the WHERE clause is executed before the SELECT statement. This means that when referencing an alias in the WHERE clause, it must have been defined before attempting to use it.

Original Query and Error

The following query attempts to use an alias (_year) in the WHERE clause:

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
    _year > 90

However, this query raises an error:

ERROR:  column "_year" does not exist
LINE 1: ...STRING (pk, 6, 2)::INT AS _year FROM listing WHERE _year > 90...
                                                              ^
********** Error **********

ERROR: column "_year" does not exist

This error occurs because the alias (_year) is not defined until the SELECT statement.

Overcoming the Limitation

It is not possible to directly reference an alias in the WHERE clause due to the execution order of SQL statements. To avoid this limitation, rewrite the query as follows:

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
SUBSTRING(pk, 6, 2)::INT > 90

In this query, the expression to calculate _year is repeated in the WHERE clause. While not as concise as using an alias, this ensures that the value of _year is calculated before it is used in the comparison.

The above is the detailed content of Can I Use Aliases in SQL WHERE Clauses?. For more information, please follow other related articles on the PHP Chinese website!