Different result depending on order of joins

Nicklas Avén <nicklas.aven@xxxxxxxxxxxxx> · Fri, 22 May 2015 11:51:34 +0200



	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