Re: Different result depending on order of joins

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.

How do I unsubscribe from this list, please?

On 22 May 2015 at 11:46, Nicklas Avén <nicklas(dot)aven(at)jordogskog(dot)no> wrote:

>
>
> 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
>

--
Tim Rowe