Different result depending on order of joins

From: Nicklas Avén <nicklas(dot)aven(at)jordogskog(dot)no>
To: pgsql-general(at)postgresql(dot)org
Subject: Different result depending on order of joins
Date: 2015-05-22 09:51:34
Message-ID: 201505220951.t4M9pYZX015783@mail2.space2u.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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&eacute;n

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2015-05-22 10:17:55 Re: Different result depending on order of joins
Previous Message Oleg Bartunov 2015-05-22 09:37:25 Re: Grouping By Similarity (using pg_trgm)?