Aliases with correlated subqueries

Question

I am looking at an example of correlated subquery with aliases from a PostgreSQL book:

bpsimple=# SELECT * FROM orderinfo o,
bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c
bpsimple=# WHERE c.customer_id = o.customer_id;

Why is the c alias used twice here? Isn't the one inside the parenthesis enough?

No, the one inside the parenthesis is redundant. The one outside the parenthesis is the alias for the derived table and is enough. — ypercubeᵀᴹ, Mar 21 '14 at 12:37
c as an alias for customer is overwritten by c as alias for customer where town ='Bingham' so they are not the same. — Mihai, Mar 21 '14 at 12:38
And it's not a correlated subquery, it's an (uncorrelated) subquery. Usually called a "derived table". — ypercubeᵀᴹ, Mar 21 '14 at 12:38
It is not required here, though it does not harm. If the inner query were more complicated then having something aliased to c inside and the overall derived table referred to as c too causes potential confusion (like this) so should be avoided. — David Spillett, Mar 21 '14 at 14:43

score 3 · Accepted Answer · edited Apr 13 '17 at 12:42

As @ypercube already explained, the subquery has no reference to columns in the outer query, it can be processed independently. So it is not a "correlated subquery". Some call that a "derived table", or just "subquery".

SELECT *
FROM   orderinfo o
    , (SELECT * FROM customer c1 WHERE town = 'Bingham') c2
WHERE  c2.customer_id = o.customer_id;

As to your questions:

c1 is a table alias for customer in the subquery, short for customer AS c1. The key word AS has been omitted which is fine since it would be just noise for a table alias. I quote the manual on "Omitting the AS Key Word":

In FROM items, both the standard and PostgreSQL allow AS to be omitted before an alias that is an unreserved keyword. But this is impractical for output column names, because of syntactic ambiguities.

The example probably goes to demonstrate visibility: only the outer table alias is visible in the outer WHERE clause, so there is no naming conflict with c used twice.
Otherwise, c1 is useless here since nothing refers to it. You can just drop it.
c2 is another table alias for the "derived table". This one is mandatory since Postgres requires a name for every used table, and a subquery has none until you name it.

The example is not very good overall. Such a query should rather use an explicit JOIN clause and the subquery is just useless. This would be better, shorter and faster:

SELECT *
FROM   orderinfo
JOIN   customer  c USING (customer_id)
WHERE  c.town = 'Bingham';

The only difference: customer_id is listed once instead of twice in the result (due to the USING clause), which would be preferable since it is completely redundant in this case.

Details in the manual about SELECT.

Aliases with correlated subqueries

1 Answers1

Linked