Which is better join or in?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Which is better join or in?

  1. #1
    Join Date
    Jul 2000
    Location
    Winnipeg
    Posts
    21

    Hi All.

    I want to select data from one table BUT only if there is a reference to it in another table.

    Which is better example A or Example B ?

    Table Not_want pkey, data. more_data

    Table want_from pkey, data_needed, use_data

    example A

    Select wf.data_needed, wf.use_data
    from not_want nw, want_from wf
    where nw.pkey = wf.pkey
    ;

    example B

    Select data_needed
    from want_from
    where wf.pkey in ( Select pkey from not_want )
    ;


    thank you
    Grah,

    You'll always learn something if you take your time to ask.

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Lot's of what-if's:

    How much data is there total in each table?
    What percentage of all rows returned will be in the result set?
    What does the Explain Plan access paths reveal for each example?
    What are your benchmark times for each (which performs better - wall clock)?
    Are indexes worth the while (based on total rows returned in the result set) or SORTMERGE after full scans?

    Benchmark!

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Grah

    Select data_needed
    from want_from
    where wf.pkey in ( Select pkey from not_want )
    ;
    Oracle almost always converts sub-queries into join before proceed

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Instead of IN use EXISTS

    select * from table1 where exists(select 'x' from table2 where
    table1.col1=table2.col1);

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    This question has some reason with rule based optimazer.
    in CBO, this is my mind, need get execution plan in any way.

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    I feel , using a dirct join comparison is better that using in clause ( reason could be based on the algoritham it follows for obvious reasons , it does lot of permutations in IN CLAUSE ) . u can check the same command on two different times by setting the clock on on your sqlplus . after clearing the sql parse )
    siva prakash
    DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width