Why Can't I Use Aliases in My SQL WHERE Clause?

Unable to Reference Aliases in WHERE Clause

When attempting to reference aliases in the WHERE clause of a SQL statement, users may encounter an error indicating that the specified column does not exist. This error arises because SQL execution adheres to a specific order, with the WHERE clause being evaluated before the SELECT clause. Consequently, aliases defined in the SELECT clause are not recognizable at the time of WHERE clause execution.

Cause

In the example provided, the following query attempts to filter rows based on an alias (_year) created in the SELECT clause:

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

This query will result in the following error:

ERROR: column "_year" does not exist

Solution

To resolve this issue and reference aliases in the WHERE clause, the query must be rewritten to use the underlying column instead:

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

By directly referencing the column (pk) used to create the alias (_year), the query will execute successfully and retrieve the desired results.

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