[GENERAL] LEFT and RIGHT JOIN

[Misa Simic]

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