Re: LEFT and RIGHT JOIN

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.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.