Home > mailing lists

Thread: Different result depending on order of joins

Different result depending on order of joins

From

Nicklas Avén

Date:

22 May 2015, 12:51:39

Hallo

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?

Thanks

Nicklas Avén

Re: Different result depending on order of joins

From

Albe Laurenz

Date:

22 May 2015, 13:18:04

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

Re: Different result depending on order of joins

From

Nicklas Av\xE9n

Date:

22 May 2015, 13:46:33

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

Re: Different result depending on order of joins

From

Tim Rowe

Date:

22 May 2015, 16:07:06

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@jordogskog.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

Re: Different result depending on order of joins

From

"Christofer C. Bell"

Date:

22 May 2015, 16:15:19

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@gmail.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.aven@jordogskog.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

Re: Different result depending on order of joins

From

John McKown

Date:

22 May 2015, 16:29:09

Start here:

http://www.postgresql.org/community/lists/subscribe/

Change the drop down from SUBSCRIBE to UNSUBSCRIBE and put in the rest of the required information.

On Fri, May 22, 2015 at 8:06 AM, Tim Rowe <digitig@gmail.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.aven@jordogskog.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

My sister opened a computer store in Hawaii. She sells C shells down by the seashore.

If someone tell you that nothing is impossible:

Ask him to dribble a football.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown