LEFT and RIGHT JOIN

Misa Simic <misa.simic@gmail.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

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.

Thanks Tom,

Yes you are right... I wanted: t1 left join (t2 inner join t3)
Is there a way to "say" that? I mean to me, it is logical and without
brackets... i.e. t1 left join t2 inner join t3 left join t4, I would read
as: t1 left join (t2 inner join t3) left join t4 .... (Like INNER has
advantage on OUTER...)...

(But I believe my logic is wrong - because of I have tried that on
different SQL engines...)

Or the only way to "say" that is to dont care is there INNER or not... Just
put brackets (in head) from left to right in row how table shows in line
and if you want SELECT all from 1 table make sure it is on the end of the
line (in that case you will need to use RIGHT of course)?

Thanks,

Misa

2012/6/29 Tom Lane <tgl@sss.pgh.pa.us>

Show quoted text

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

Misa Simic <misa.simic@gmail.com> writes:

Yes you are right... I wanted: t1 left join (t2 inner join t3)
Is there a way to "say" that?

Sure, just add the parentheses.

t1 left join (t2 inner join t3 on t2-t3-condition) on t1-t2-condition

I mean to me, it is logical and without
brackets... i.e. t1 left join t2 inner join t3 left join t4, I would read
as: t1 left join (t2 inner join t3) left join t4 .... (Like INNER has
advantage on OUTER...)...

Well, that might or might not be more intuitive, but the SQL standard
says these bind equally, left to right, unless you use parens.

regards, tom lane

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@yahoo.com>

Show quoted text

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.