LEFT and RIGHT JOIN

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

Responses

Browse pgsql-general by date

  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