Search Postgresql Archives

Re: Different result depending on order of joins

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.aven@xxxxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux