ALL ANY SOME OPERATORS IN SUBQUERY

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.

Example:

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.

Example:

ALLANYSOME_01
pic 1.1
ALLANYSOME_02
pic 1.2

ALL:

  • 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.

Example:

ANY and SOME:

  • 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.

Example:

  •  ALL, ANY & SOME query results are captured in below screen picture.
ALLANYSOME_03
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: