LEFT and RIGHT JOIN

Misa Simic <misa.simic@xxxxxxxxx> · Fri, 29 Jun 2012 23:36:05 +0200

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