From: | Misa Simic <misa(dot)simic(at)gmail(dot)com> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | LEFT and RIGHT JOIN |
Date: | 2012-06-29 21:36:05 |
Message-ID: | CAH3i69msZTFDcnmgW0vJm6YjVMni+rW=xBgWtJNoKGee26JpFg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-29 21:56:47 | Re: LEFT and RIGHT JOIN |
Previous Message | David Johnston | 2012-06-29 20:45:42 | Catalog Bloat in Development - Frequently dropping/adding schemas and objects |