7 Replies Latest reply: Feb 18, 2014 7:38 AM by Karthick_Arp RSS

    Join vs Subquery

    Suresh Gowtham

      Hi everyone,

      Which is best join or subquery based on performance

        • 1. Re: Join vs Subquery
          Brian Bontrager

          As with many things, "it depends".  In some cases the optimizer will process them exactly the same. Do you have a specific example in mind?

          • 2. Re: Join vs Subquery
            GregV

            Hi,

             

            Usually when I don't need to retrieve columns from the second table I go for semi joins (with exists condition for example). As far as performance is concerned, this will depend on your query. But you can try the different possibilities and see which one performs the best.

            • 3. Re: Join vs Subquery
              onkar.nath

              Suresh,

               

              As Brian and Greg has already mentioned that the simple answer is "it depends". It can not be generalized. Join may work faster in certain cases but then subquery can also be faster in other cases. So unless you have specific query to work upon, it may not be generalized.  As Greg mentioned, if I dont have any column in the final output from second or third or other tables except one table, I would say use subquery.

               

              Check this out : Ask Tom "joins vs subquery , Exists clause "

               

              Onkar

              • 4. Re: Join vs Subquery
                Karthick_Arp

                Join and Sub Query are completely different things. Not sure how you could compare them.

                 

                Join is a method that helps you to retrieve data from multiple tables. Subquery is a select statement nested within another SQL statement. So in my opinion they two don't have any relevance.

                 

                Not sure what you are looking at.

                • 5. Re: Join vs Subquery
                  Suresh Gowtham

                  Hi thanks to all for replied i can understand wat ur trying to say....

                  my question is In some scenario we can use any one " JOIN OR SUBQUERY" at that time which will be prefered

                  For example consider this ex:

                  I hav two table

                   

                  SQL> select * from boats;

                   

                         BID BNAME      COLOR

                  ---------- ---------- ----------

                         101 seatiger   yellow

                         102 windsong   black

                         103 seagod     blue

                         104 winddancer red

                         105 secondwind green

                   

                  SQL> select * from reserves;

                   

                         SID        BID DAY

                  ---------- ---------- ---------

                          22        101 10-OCT-08

                          31        101 10-JUN-09

                          31        102 12-JUN-09

                          31        103 14-JUN-09

                          31        104 16-JUN-09

                          31        105 18-JUN-09

                          58        102 11-DEC-08

                          71        101 25-JUL-09

                          71        105 15-JUL-09

                          85        104 12-JUN-09

                          85        105 14-JUN-09

                          95        102 10-JUL-09

                          95        103 10-JUL-09

                          95        104 10-JUL-09

                   

                  14 rows selected.

                   

                  Now i want to display "SID" who are all reserved "RED" boats

                   

                  So i can write the query in two ways

                  Using SubQuery

                   

                   

                    1  SELECT SID FROM reserves

                    2  where bid = (SELECT bid from boats

                    3*                    WHERE UPPER(color)='RED')

                  SQL> /

                   

                         SID

                  ----------

                          31

                          85

                          95

                   

                  Using Join

                   

                  SQL> SELECT r.sid FROM reserves r,boats b

                    2  WHERE r.bid=b.bid AND upper(color)='RED';

                   

                         SID

                  ----------

                          31

                          85

                          95

                   

                  So both r getting the same result but which is best based on performance.

                  In my knowdledge subquery is best in the scenario.

                  Most of the people are says always  join is the best when compared to sub query.

                  • 6. Re: Join vs Subquery
                    Roger

                    Most of the people are says always  join is the best when compared to sub query.

                     

                     

                    Most people say....it depends.

                     

                    Take a look at the execution plan and then you'll see, that often it is exactly the same.

                     

                    So there is no general "join is better than subquery" or "subquery is better than join" thing.

                     

                    hth

                    • 7. Re: Join vs Subquery
                      Karthick_Arp

                      Your two query are not the same.

                      select sid

                        from reserves

                      where bid = (select bid

                                      from boats

                                     where upper(color)='RED');

                       

                      Above query will return error if there is more that one row for RED. So you must use IN. Using IN lets check the execution plan for both the query.

                      PLAN_TABLE_OUTPUT
                      ---------------------------------------------------------------------------------------------------
                      SQL_ID  4cgj029yc1khr, child number 0
                      -------------------------------------
                      select sid   from reserves  where bid in (select bid                  from boats
                                   where upper(color)='RED')

                      Plan hash value: 2724745698

                      --------------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
                      |   1 |  TABLE ACCESS BY INDEX ROWID| RESERVES     |     3 |    21 |     1   (0)| 00:00:01 |
                      |   2 |   NESTED LOOPS              |              |     1 |    17 |     3   (0)| 00:00:01 |
                      |*  3 |    TABLE ACCESS FULL        | BOATS        |     1 |    10 |     2   (0)| 00:00:01 |
                      |*  4 |    INDEX RANGE SCAN         | RESERVES_BID |     3 |       |     1   (0)| 00:00:01 |
                      --------------------------------------------------------------------------------------------

                      Predicate Information (identified by operation id):
                      ---------------------------------------------------

                         3 - filter(UPPER("COLOR")='RED')
                         4 - access("BID"="BID")


                      23 rows selected.

                       

                      PLAN_TABLE_OUTPUT
                      ---------------------------------------------------------------------------------------------------
                      SQL_ID  d8887x8z7uwuc, child number 0
                      -------------------------------------
                      select r.sid   from reserves r      , boats b  where r.bid=b.bid    and
                      upper(color) = 'RED'

                      Plan hash value: 2724745698

                      --------------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
                      |   1 |  TABLE ACCESS BY INDEX ROWID| RESERVES     |     3 |    21 |     1   (0)| 00:00:01 |
                      |   2 |   NESTED LOOPS              |              |     1 |    17 |     3   (0)| 00:00:01 |
                      |*  3 |    TABLE ACCESS FULL        | BOATS        |     1 |    10 |     2   (0)| 00:00:01 |
                      |*  4 |    INDEX RANGE SCAN         | RESERVES_BID |     3 |       |     1   (0)| 00:00:01 |
                      --------------------------------------------------------------------------------------------

                      Predicate Information (identified by operation id):
                      ---------------------------------------------------

                         3 - filter(UPPER("COLOR")='RED')
                         4 - access("R"."BID"="B"."BID")


                      23 rows selected.

                       

                      Its exactly the same. This is just for this case for a database that I am using. In a database with different configuration and data things may be completely different.

                       

                      Always remember there is no rule of thumb when it comes to performance. Oracle offers different tools for different purpose. Each may act in a different manner when put in different situation. So its always better to check the execution plan.


                      My db version

                      SQL> select * from v$version where rownum = 1;

                       

                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi