Amit
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);
select * from tbl1
where id_1 in (select id_2 from tbl2 where id_2 between 2000 and 3000);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much experts
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.