Re: Different result depending on order of joins

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