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?
 
SujithConnect With a Mentor Data 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
 
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
 
sdstuberConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.