Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 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 at 12:38
1  
And it's not a correlated subquery, it's an (uncorrelated) subquery. Usually called a "derived table". –  ypercube Mar 21 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 at 14:43

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.