Re: left outer join

Actually ... if you were to Explain Plan the queries ... you would find out that the join you are trying is substantially less efficient. And, in fact, in almost all cases in Oracle an inner join is less efficient than its restatement using EXISTS.

You don't need a left outer join ... you need the right answer using the most efficient optimizer plan. If you try to do SQL Server in Oracle all you will do is write really lame code and bring the database to its knees. In fact it can get so bad it will start behaving like SQL Server.

BTW The normal outer join in Oracle uses the (+) as in

select t1.bla, t2.blub
from table1 t1 table2 t2
where t1.bla = t2.hihi (+)

And I suspect for your usage that you really don't understand rownum in Oracle either. Be sure you do before you use it. There is no rownum < 1.

Daniel Morgan

Andre Grosse wrote:

> nothing at all...but it dosnt help with the original select, i
> constructed this example from that.
>
> select t11.bla, t12.blub
> from table1 t11 left join table1 t12
> on t12.id in (select t2.hmpf
> from table2 t2
> where rownum <= 1 and (t2.hihi = t11.bla))
>
> would be more specific. its really a needed left join. :)
>
> --
> Andre Grosse
>
> Niall Litchfield wrote:
>
> > what is wrong with
> >
> > select count(*) from table1
> > where exists(
> > select 1 from table2 where table2.id=1);
> >
> >
Received on Tue Jan 15 2002 - 09:02:11 CST