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
anshumaEngineeringAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
A few examples.

If you have specific questions; post sample data and expected results.
SQL> select * from tbl1;
 
      ID_1
----------
       100
       200
       400
       500
 
SQL> select * from tbl2;
 
      ID_2
----------
        10
       200
       400
        50
 
SQL> select * from tbl1
  2  where id_1 in (select id_2 from tbl2);
 
      ID_1
----------
       200
       400
 
SQL> select tbl1.*
  2  from tbl1, tbl2
  3  where id_1 = id_2;
 
      ID_1
----------
       200
       400
 
SQL> select * from tbl1
  2  where id_1 NOT IN (select id_2 from tbl2);
 
      ID_1
----------
       100
       500
 
SQL> 
SQL> select tbl1.*
  2  from tbl1 left outer join tbl2
  3  on (id_1 = id_2)
  4  where id_2 is null;
 
      ID_1
----------
       500
       100
 
SQL> 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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.
0
anshumaEngineeringAuthor Commented:
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);
0
sdstuberCommented:
select distinct tbl1.*
from tbl1, tbl2
where tbl1.id_1 = tbl2.id_2
and tbl2.id_2 between 2000 and 3000
0
anshumaEngineeringAuthor Commented:
Thank you very much experts
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.