Re: Different result depending on order of joins

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