Link to home
Start Free TrialLog in
Avatar of Amit
AmitFlag for United States of America

asked on

Examples of avoiding subselects and using joins instead on oracle

Hi,

I am newbie in Oracle SQL and I need to know couple of examples where I can use join queries insetad of writing the nested subqueries using the "in" clause. I heard that the "in" sub queries are very expensive. Could you show me or point me to some example where a nested sbuquery can be written as a join query. Please note that my "in" condition returns mutliple rows and not just one single row

thanks
-anshu
ASKER CERTIFIED SOLUTION
Avatar of Sujith
Sujith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

IN and EXISTS clauses are not necessarily more expensive or cheaper than joins.

In fact,  if you look at query plans you may find they resolve to the exact same steps in many (most?) queries.

The reverse is most definitely not true for NOT IN and NOT EXISTS,  as they are not functionally equivalent nor do they correspond well with joins but that's a different question.

Fortunately, it's easy to verify which if either is more efficient.  Simply write them both.
Use autotrace to measure the number of gets and sort operations.  Definitely avoid the habit of trusting vague rules of thumb.  (That includes suggestions given here on EE.)  If you can test a premise, then do so.
Avatar of Amit

ASKER

Thank  you experts. I have one more question for sujith before I close this question how will I implement the following using a join ?

select * from tbl1
  where id_1 in (select id_2 from tbl2 where id_2 between 2000 and 3000);
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amit

ASKER

Thank you very much experts