Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unanswered: exists and in clause

    Hi all,
    I am confused where to use "exists" and where to use "in" clause...they sound similar...i am totally confused..will you please make me clear ?

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use EXISTS when you need to ensure something exists

    use IN when you need something to be in a list of things

    examples

    SELECT name FROM students
    WHERE EXISTS (SELECT * FROM awards WHERE name = students.name)

    SELECT name FROM students
    WHERE teacher IN ( 'Smith','Jones' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    As a rule of thumb, the subquery of an "IN" will almost never be correlated, while the subquery for an "EXISTS" will always be correlated.
    "Correlation" means that the subquery contains a condition that refers to (one of the) tables in the outer query.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    EXISTS does not always have to be correlated

    SELECT apology FROM excuses WHERE sincerity='high'
    AND EXISTS (SELECT game FROM nfl WHERE gamedate=current_date)

    i.e. i can't talk to you tonight if there's a game on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Agreed. But that would be an "atypical" case. That's why I'm calling it just a "rule of thumb"; the exceptions prove the rule...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see

    so when you say "always", you really mean "always, except when it isn't"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    when you say "always", you really mean "always, except when it isn't"
    Let's say that I mean "always, unless you are 100% aware of what you are doing and why you're doing it that way and not differently".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    SELECT apology FROM excuses WHERE sincerity='high'
    AND EXISTS (SELECT game FROM nfl WHERE gamedate=current_date)
    Good lord - a single game takes all day?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you're thinking of sticket, um, wicket, er, ah, picket, um, oh yeah, cricket

    "a hunnert and fifty two over and under with nobody out and a man on third after the fifth day of the test"

    nfl games are scheduled on a single day only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    As a rule of thumb, the subquery of an "IN" will almost never be correlated, while the subquery for an "EXISTS" will always be correlated.
    "Correlation" means that the subquery contains a condition that refers to (one of the) tables in the outer query.
    Anyway correlation means that the subquery will be executed for every produced row in the main query, where with 'IN' first the subquery wil be executed once and after that the main query... Can make a big difference in performance if both querytypes are interchangeable. (And to my knowledge is the EXISTS construct designed for the subquery to contain a reference to a column in the main query. If it can evaluate to TRUE without this reference doesn't imply something meaningful will be produced)

    Regards

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually "subquery will be executed for every row in the main query" is a popular misconception

    subqueries are usually executed as joins

    but you are right, a non-correlated subquery can be evaluated once only, so it is much more efficient

    however, please do not suggest that a non-correlated subquery isn't meaningful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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