2015-05-22 skrev Albe Laurenz :
Nicklas Avén wrote: >> I was a little surprised by this behavior. >> Is this what is supposed to happen? >> >> This query returns what I want: >> >> with >> a as (select generate_series(1,3) a_val) >> ,b as (select generate_series(1,2) b_val) >> ,c as (select generate_series(1,1) c_val) >> select * from a >> inner join c on a.a_val=c.c_val >> full join b on a.a_val=b.b_val >> ; >> >> I get all values from b since it only has a full join and nothing else. >> >> But if I change the order in the joining like this: >> >> with >> a as (select generate_series(1,3) a_val) >> ,b as (select generate_series(1,2) b_val) >> , c as (select generate_series(1,1) c_val) >> select * from a >> full join b on a.a_val=b.b_val >> inner join c on a.a_val=c.c_val >> ; >> >> also b is limited to only return value 1. >> >> I thought that the join was defined by "on a.a_val=c.c_val" >> and that the relation between b and the rest wasn't affected by that last >> inner join. >> >> I use PostgreSQL 9.3.6 >> >> Is this the expected behavior? > >Yes. > >In >http://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-JOIN >you can read: > > "In the absence of parentheses, JOIN clauses nest left-to-right." > >So the first query will first produce > > a_val | c_val >-------+------- > 1 | 1 > >and the FULL JOIN will add a row for b_val=2 with NULL a_val. > >The second query will first produce > > a_val | b_val >-------+------- > 1 | 1 > 2 | 2 > 3 | > >an since none but the first row matches a_val=1, you'll get only that row in >the result. > >Yours, >Laurenz Albe Thank you! Sorry for not finding it myself, but now I understand why it behaves like this :-) Thanks Nicklas
