Results 1 to 6 of 6

Thread: which is more suitable IN or EXIST in SQL?

  1. #1
    Junior Member
    Join Date
    Jun 2007
    Answers
    2

    which is more suitable IN or EXIST in SQL?

    which is more suitable IN or EXIST in SQL?


  2. #2
    Junior Member
    Join Date
    Jun 2007
    Answers
    28

    Re: which is more suitable IN or EXIST in SQL?

    Hi
    EXIST is fast compare to IN


  3. #3
    Junior Member
    Join Date
    May 2007
    Answers
    16

    Re: which is more suitable IN or EXIST in SQL?

    which is more suitable in or exist in sql?
    IN clause is generally used to return values which are used as parameters for selecting another set of records. As follows
    SELECT emp_name FROM employees where emp_id IN ( select emp_id FROM another table WHERE some_condition )

    And EXISTS is used as a decision making statement as follows

    IF EXISTS ( select emp_name from emp where emp_id=1)
    BEGIN
    do something;
    END
    ELSE
    BEGIN
    do another thing;
    END

    So these statements are basically used for two different purposes. One may want to ask is
    "is join better than in?"
    Answer is yes. Joins are always faster than IN. So the first qry can be written using JOIN as follows :

    select e.emp_name from employees e join another_table a on e.emp_id = a.emp_id and a.some_col = something

    Above statement executes much faster than IN.

    Now coming to the second statement EXISTS the catch is ; always use
    IF EXITS ( select emp_name from emp where emp_id=1)
    BEGIN
    do something;
    END
    over following statement

    IF EXITS ( select count(*) from emp where emp_id=1)
    BEGIN
    do something;
    END
    because second EXISTS statement does a table scans to get the count of rows and first statemet returns true as soon as it finds first match.


    HTH
    Regards,


  4. #4
    Junior Member
    Join Date
    Jun 2007
    Answers
    2

    Thumbs up Re: which is more suitable IN or EXIST in SQL?

    Quote Originally Posted by vcyogi View Post
    Hi
    EXIST is fast compare to IN
    Thank you very much


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

    Re: which is more suitable IN or EXIST in SQL?

    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.


  6. #6
    Junior Member
    Join Date
    Jun 2007
    Answers
    18

    Re: which is more suitable IN or EXIST in SQL?

    EXIST would a faster option because IN has return data from table where as EXIST needs to return only a literal which makes it much faster


  7.  Sponsored Ads
    Ad


     

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