PostgreSQL – Using Aliases with Correlated Subqueries

aliaspostgresqlsubquery

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?

Best Answer

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.

Related Question