Specify a quantified comparison.

 

Syntax

 

<quantified comparison predicate> ::=

 

<quantified comparison predicate part 2> ::=

 

<quantifier> ::=

 

|

 

ALL

{ SOME | ANY }

 

 

Notes

 

The degree of the row value predicand and table subquery shall be 1.



 

The data types shall be comparable.



 

If ALL is specified, then the predicate is evaluated as follows:



 


a) If the result of the subquery is empty or the comparison is true for every row in the subquery, then the result is TRUE.



 


b) If the comparison is false for at least one row in the subquery, then the result is FALSE.



 


c) If any of the values returned by the subquery is null and none of the comparisons are false, then the result is UNKNOWN.



 

If SOME or ANY is specified, then the predicate is evaluated as follows:



 


a) If the comparison is true for at least one row in the subquery, then the result is TRUE.



 


b) If the result of the subquery is empty or the comparison is false for every row in the subquery, then the result is FALSE.



 


c) If any values returned by the subquery is null and none of the comparisons are true, then the result is UNKNOWN.



 

If the row value predicand is null, then the predicate evaluates to UNKNOWN.



 

 

Examples

 

1)

The following example selects students enrolled in all available courses:



 


SELECT studentID, studentName


FROM students s


WHERE studentID = ALL (




SELECT e.studentID




FROM courses c


LEFT JOIN enrolls e on




e.courseID = c.courseID AND e.studentID = s.studentID




)



 

2)

The following example selects students enrolled in at least one course:



 


SELECT studentID, studentName


FROM students s


WHERE studentID = ANY (




SELECT e.studentID




FROM courses c


LEFT JOIN enrolls e on




e.courseID = c.courseID AND e.studentID = s.studentID




)



 

 


 

Conformance

 

SQL:2003 standard

-

Core SQL