Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: EXISTS vs ALL vs ANY

    I'm having a hard time wrapping my head around the practical differences between EXISTS, ALL, and ANY. I use EXISTS frequently and see it as an alternative to IN. Perhaps someone here can explain better than the MSDN.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    ALL and ANY will allow you to do several different kinds of comparisons to a set of data. EXISTS is kind of like a subset of the function of ANY/SOME. With EXISTS, you get a true returned, if there is any record in the subquery. Technically, you could write
    Code:
    select *
    from table
    where exists (select 1)
    This would return the whole table, as 1 exists for all records. Usually, you do a lookup of some sort, such as
    Code:
    select *
    from table1
    where exists (select * from table2 where table2.col1 = table1.col1)
    But, what if you wanted to know whether the table1.col1 value was greater than any of the values in table2.col1? You could write something a little more convoluted as
    Code:
    select *
    from table1
    where not exists (select * from table2 where table2.col1 > table1.col1)
    Or, you could write
    Code:
    select *
    from table1
    where table1.col1 > ALL (select col1 from table2)
    Does that make it any better? The key difference is the comparison is taken out of the subquery, and placed before the keyword ALL/SOME/ANY. Of course, this means you can't really have a select * in the subquery for ALL/ANY/SOME.

Posting Permissions

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