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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
