ALL, ANY and SOME operators are commonly known as comparison operators and are for subquery usage only. Before discussing about the these operators, lets have a look at types of subqueries. source-code

  1. Nested (Normal subquery)
  2. Correlated subquery

Nested Subquery:

  • A subquery can be nested in SELECT, INSERT, UPDATE and DELETE.
  • The subquery returns result for the outer query to main so subquery executes first then outer query executes next.
  • Simple subquery is does not use values from main query and is being just calculated only once.
  • This can independently work in where clause with using operators.
  • SQL Server supports up to 32 nested level of subquery in a single statement.


Correlated Subquery:

  • This is known as repeating subquery.
  • Co-related subquery loop under main query, therefore correlated subquery executes on each iteration of main query.
  • In this case inner and outer query depends on each other and are get executed simultaneously.
  • Correlated subquery is a performance eater so needed a full consideration before using it.


pic 1.1
pic 1.2


  • This subquery operator returns TRUE for each values to main query in which every record meets the condition.
  • Below query returns all the products which are at least ordered once or never ordered so far (1 >= ALL)
  • (2>= ALL) : All the products which are ordered twice or less than that or never ordered.



  • ANY and SOME functions same. SOME is an ISO standard keyword equivalent to ANY.
  • The ANY operator works much like IN operator.  ANY returns TRUE if at least one value returned by the subquery makes the predicate TRUE.
  • The both operators works identical.


  •  ALL, ANY & SOME query results are captured in below screen picture.
pic 1.3

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: