Results 1 to 6 of 6

Thread: IN or EXISTS

  1. #1
    Junior Member
    Join Date
    May 2006
    Answers
    9

    IN or EXISTS

    Which clause is better to use in subqueries of SQL, IN or EXISTS clause? Do they have the same output?


  2. #2
    Expert Member
    Join Date
    May 2006
    Answers
    114

    Re: IN or EXISTS

    When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query .When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query . So determine which gives better performance in rule-based optimization one of the steps could be to consider or analyze the rows the inner query will return in comparison to the outer query. Decision made based on this will increase the performance.


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: IN or EXISTS

    It has been my experience that as a general rule of thumb, Oracle processes the IN operator faster than the EXISTS operator.


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: IN or EXISTS

    To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.


  5. #5
    Junior Member
    Join Date
    May 2007
    Answers
    1

    Re: IN or EXISTS

    The EXISTS construct is a TSQL function that returns a Boolean value based upon the subquery inside of the parentheses. If the subquery returns any rows, the construct returns true else it will return false. To my experience I have always found use of Exists construct faster compared to IN.


  6. #6
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: IN or EXISTS

    Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset (nested loops, merge, whatever). If the table in the subquery is
    small, and the table in the main query is large, then IN usually makes
    sense.

    Using "EXISTS", the optimizer generally runs the main query first, then
    applies that dataset to the subquery. If the table in the subquery is
    large, and the table in the main query is small, then EXISTS usually makes
    sense.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact