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".
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":
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.
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:
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