[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Examples of avoiding subselects and using joins instead on oracle

Posted on 2008-11-09
5
Medium Priority
?
367 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:anshuma
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
sujith80 earned 1200 total points
ID: 22918713
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 22921792
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
 

Author Comment

by:anshuma
ID: 22935491
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 22936338
select distinct tbl1.*
from tbl1, tbl2
where tbl1.id_1 = tbl2.id_2
and tbl2.id_2 between 2000 and 3000
0
 

Author Closing Comment

by:anshuma
ID: 31514868
Thank you very much experts
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month19 days, 4 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question