CODEWITHSUNDEEP
sqlpostgresqlsubquery
user3747260
user3747260

Reputation: 487

PostgreSQL why can table aliases be referenced inside subquery WHERE but not FROM

I'm wondering why a reference to a table alias in the FROM clause is invalid, but it is valid to reference a column using the alias in the WHERE clause within the same subquery.

Here are two example queries: the first produces the error relation "t1" does not exist, but the second is valid:

SELECT * FROM(SELECT * FROM existing_table WHERE conditioncol < 1000) AS t1
WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.conditioncol < existing_table.conditioncol + 50)


SELECT * FROM(SELECT * FROM existing_table WHERE conditioncol < 1000) AS t1
WHERE EXISTS(SELECT 1 FROM existing_table WHERE t1.conditioncol < existing_table.conditioncol + 50)

Note that the only difference in these two queries is that the first has FROM t1 whereas the second has FROM existing_table in the subquery.

Upvotes: 2

Views: 1691

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You are confusing table aliases with qualified column names.

What you are referencing in a correlation clause is a column name. The table alias is differentiating where the column comes from.

If you want to reference the same subquery in more than one place, then use a CTE:

with t as (
      select . . .
     )
select . . .
from t
where exists (select 1 from t t2 where . . . );

Upvotes: 1

Related Questions