Sorry to post this on the list, but I can't find any way of unsubscribing -- I've looked in messages, on the community home pages and on a web search, but all I find is a lot of other subscribers with the same problem.
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 :-)
