Re: LEFT and RIGHT JOIN

Thanks Salah, David on your detailed answers...

I will need to change my mindset to just read tables from left to right...
I thought INNER has advantage to OUTER regardles in which order are tables
in the query... And just take data from LEFT or RIGHT...

So basically, if there is t1 left join t2 inner joint t3... to me was the
same as t2 inner join t3 right join t1 what obviusly is not the case :)

Many thanks,

Misa

2012/6/30 David Johnston <polobo(at)yahoo(dot)com>

>
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Misa Simic
> Sent: Friday, June 29, 2012 5:36 PM
> To: pgsql
> Subject: [GENERAL] LEFT and RIGHT JOIN
>
> Hi,
>
> I have met some strange situation... Could someone explain difference
> between LEFT and RIGHT JOIN? I thought it is just from whitch side we are
> looking in JOIN columns part... but it seems that is not the case....
>
> I have three Tables with the same structure...
>
> CREATE TABLE t1
> (
> id integer NOT NULL,
> sometext text
> CONSTRAINT t1_pk PRIMARY KEY (c1 )
> )
> WITH (
> OIDS=FALSE
> );
>
> data in tables are
> t1 t2 t3
> 1, t1row1 1, t2row1 1, t3row1
> 2, t1row2 2, t2row2
> 3, t1row3
>
>
> I want to apply next query:
>
> Get All text values from t1, relateded value from t2, in case you have
> found matched value in t2, show me related value from t3...
>
> So expecting result is:
> t1 t2 t3
> t1row1 t2row1 t3row1
> t1row2
> t1row3
>
>
> (row 2 from t2, is not in result because of there is no related row in t3
>
>
> 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:
> t1 t2 t3
> t1row1 t2row1 t3row1
>
>
> The same result as we run:
>
> SELECT t1.sometext AS c1, t2.sometext AS c2, t3.sometext AS c3
> FROM
> t1
> INNER JOIN t2 ON t1.id = t2.id
> INNER JOIN t3 ON t2.id = t3.id
>
> 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!
>
> Could anyone light the catch?
>
> Thanks,
>
> Misa
>
> ======================================================
>
> >>FROM t1
> >>LEFT JOIN t2 ON t1.id = t2.id
> >>INNER JOIN t3 ON t2.id = t3.id
>
> The LEFT JOIN returns all records from t1 and only matching records from
> t2. The join on (t2.id = t3.id) requires that a record was present on
> t2. Because it is an inner join this effectively turns the LEFT JOIN into
> an INNER JOIN. You likely want the following:
>
> FROM t1
> LEFT JOIN
> (t2 INNER JOIN t3 ON t2.id = t3.id) t23
> ON t1.id = t23.id
>
> In general whenever you starting mixing LEFT/RIGHT joins with INNER joins
> it is wise to use grouping in order to enforce the order of joining. The
> exception (which is common) is when you can write the query so that all
> INNER JOINs are listed first and then all OUTER JOINs are listed at the
> end. Furthermore, you should use grouping if an OUTER JOIN references
> another OUTER JOIN. If the OUTER JOINs only reference INNER JOIN tables
> then it safe to omit groups.
>
> FROM t1
> INNER JOIN t2
> INNER JOIN t3
> LEFT JOIN t4 ON t[1-3].col = t4.col
> LEFT JOIN t5 ON t[1-3].col = t5.col -- You should probably group if this
> references t4 instead of t[1-3]
>
> Because t3 requires t2 in your example you cannot do this (i.e., list the
> inner join on t3 before the outer join on t2) and thus you need to consider
> grouping to ensure you get the desired results.
>
> Your RIGHT JOIN example mitigates this because you indeed list the INNER
> JOIN before the OUTER JOIN.
>
> FROM (t3 INNER JOIN t2) RIGHT JOIN t1
>
> which is equivalent to my revision:
>
> FROM t1 LEFT JOIN (t2 INNER JOIN t3)
>
> in both these cases t1 is on the "INCLUDE ALL" side of the OUTER JOIN and
> t2 and t3 are INNER JOINed first and the combination is OUTER JOINed to t1.
>
> Pairing occurs top-to-bottom by default (the planner can reorder IF it
> does not change the semantics/logic of the query), so your incorrect
> example logically means: FROM (t1 LEFT JOIN t2) INNER JOIN t3; and as I
> mentioned above the INNER JOIN is between a field on t3 and one on t2 (that
> cannot be null). Since t2 cannot be null (when there is a match) there are
> no valid solutions where a record exists on t1 but not on t2 and thus the
> LEFT JOIN is in effect changed to an INNER JOIN.
>
> David J.
>
>
>
>
>
>
>