Re: Different result depending on order of joins

Tim,

You just need to go back to the mailing list page on the PostgreSQL website:

* Mailing list page: http://www.postgresql.org/list/
* Management page for subscriptions:
http://www.postgresql.org/community/lists/subscribe/

While that URL says "subscribe", on the page itself, there's a drop-down
that allows you to select "Unsubscribe".

Best of luck!

On Fri, May 22, 2015 at 8:06 AM, Tim Rowe <digitig(at)gmail(dot)com> wrote:

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

--
Chris

"If you wish to make an apple pie from scratch, you must first invent the
Universe." -- Carl Sagan