joins Vs nested queries

[Sudharsan Govindarajan]

Hi!
In case of performance which is the best, Joins or Nested Queries?

[SJ Adnams]

See point #30,
http://www.dbasupport.com/oracle/ora9i/performance/tuning-4.shtml
If your question relates to a specific piece of SQL use explain plan to find out what is happening - there may be other reasons why your query is slow.
Simon

[Sudharsan Govindarajan]

Hi Simon!
Thanks for that link. Did u mean point #20? It tells "In general, join tables rather than specifying sub-queries for them". But specifies no reason for it. Any ideas?

[SJ Adnams]

well i guess it comes down to how quickly oracle can seek out the rows that you are asking for.
if you join 2 tables on 3 values then each predicate reduces the number of rows. If you do the same with sub-queries then oracle will get the resultsets from each subquery and then perform the join.
That said, I use subquerys a lot e.g.
select a from b where c in (1,2,3) and d in ('x','y');
I've never thought about if,
select a from b where (c = 1 or c = 2 or c = ) and (d = 'x' or d = 'y');
..was any quicker.
There are plenty of other optimization steps that are made in an app before getting down to the example above.
HTH Simon