Philip Kamenarsky Philip Kamenarsky - 3 months ago 16
SQL Question

Reference alias in subquery

Let's say there's a

person
table with the columns
name
and
age
.

I'm writing a DSL that generates the following SQL:

select *
from (select * from person p1 inner join person p2 on p1.name = p2.name) as pj;


Now, I would like to be able to access
p1
and
p2
in the outer query, like this:

select *
from (select * from person p1 inner join person p2 on p1.name = p2.name) as pj
where p1.name = 'xxx'; <-- DOESN'T WORK


Something like
pj.p1.name
would be ideal. Is there a way to achieve this if I don't know the exact column names of
person
?

Answer

Use using then just pj.name or even just name

create table person (id serial, name text);
insert into person (name) values ('John'),('Mary');

select *
from (
    select *
    from
        person p1
        inner join
        person p2 using(name)
) r
where name = 'John'
;
 name | id | id 
------+----+----
 John |  1 |  1

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

If only one side of the join is necessary:

select *
from (
    select p1.*
    from
        person p1
        inner join
        person p2 using(name)
) r
where name = 'John'
;
 id | name 
----+------
  1 | John

If both join sides are necessary then use records:

select (p2).id, (p1).name -- or (p2).*, (p1).*
from (
    select p1, p2
    from
        person p1
        inner join
        person p2 using(name)
) r
where (p1).name = 'John'
;
 id | name 
----+------
  1 | John
Comments