Обсуждение: 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
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
but if we run
SELECT t1.sometext AS c1, t2.sometext AS c2, t3.sometext AS c3
FROM
t3
Result is expected!
Could anyone light the catch?
Thanks,
Misa
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 fromwhitch 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 fromt3... 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 arecord was present on t2. Because it is an inner join this effectively turns the LEFT JOIN into an INNER JOIN. You likelywant 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 enforcethe order of joining. The exception (which is common) is when you can write the query so that all INNER JOINs arelisted first and then all OUTER JOINs are listed at the end. Furthermore, you should use grouping if an OUTER JOIN referencesanother 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) andthus 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 combinationis 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 INNERJOIN 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 changedto 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>
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>
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: pgsqlSubject: [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
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.INNER JOIN t3
FROM t1
INNER JOIN t2
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.