Re: LEFT and RIGHT JOIN

Misa Simic <misa(dot)simic(at)gmail(dot)com> writes:
> If we run query:

> SELECT t1.sometext AS c1, t2.sometext AS c2, t3.sometext AS c3
> FROM
> t1
> *LEFT* JOIN t2 ON t1.id = t2.id
> INNER JOIN t3 ON t2.id = t3.id

> Result is unexpected to me:

> but if we run

> SELECT t1.sometext AS c1, t2.sometext AS c2, t3.sometext AS c3
> FROM
> t3
> INNER JOIN t2 ON t3.id = t2.id
> RIGHT JOIN t1 ON t2.id = t1.id

> Result is expected!

Perhaps you're confused about the syntactic binding of JOINs?
The first query is (t1 left join t2) inner join t3, while the last one
is (t3 inner join t2) right join t1, which is the same as
t1 left join (t2 inner join t3), which is not typically the same
thing as the first one, because inner joins don't associate in or out
of the nullable side of an outer join.

regards, tom lane