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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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

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