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