New outer join syntax 1

[avjoshi]

For some reason I am not able to translet the old outer join syntax to the new one.

Here's my original query,

SELECT count(1) 
FROM
	table0 t0,
	table1 t1,
        table2 t2
WHERE 
        t1.col1 = t0.col1(+) AND
        t1.col2 = t0.col2(+) AND
        t1.col3 = t2.col3(+);

I translated it to the new syntax as as,

SELECT count(1) 
FROM  table0 t0 
left join table1 t1 on t1.col1 = t0.col1 AND
                       t1.col2 = t0.col2
left outer join table2 t2  on t1.col3 = t2.col3;

They are giving me different results. What am I missing in the construct?

Thanks in advance.

Anand

 

[sem]

You missed OUTER in the first join

Regards, Dima

http://seminihin.narod.ru

 

[avjoshi]

Thanks Dima,

Isn't outer implied? I tried with outer in the first join. The results are still the same and different from the old syntax.

Anand

 

[dbtoo2001]

wouldn't table 1 be the driver?

SELECT count(1)
FROM table1 t1
left outer join table0 t0 on t1.col1 = t0.col1 AND
t1.col2 = t0.col2
left outer join table2 t2 on t1.col3 = t2.col3;

 

[avjoshi]

Thanks dbtoo2001. That syntax worked like a charm.

Didn't know that the left in 'left join' refers to the tables in that order. I always thought that it means left portion of the join condition.



Anand

 

[dbtoo2001]

What clued me in was that t1 was the table that you always wanted the records returned from, even when the there were no records from the others.