Specify a test for a non-empty set.

 

Syntax

 

<exists predicate> ::= EXISTS <table subquery>

 

 

Notes

 

The predicate evaluates to TRUE if the cardinality of the subquery result is greater than 0, else to FALSE.



 

 

Examples

 

1)

The following example selects students enrolled in more than 2 courses:



 


SELECT studentID, studentName


FROM students s


WHERE EXISTS (




SELECT COUNT( * )


FROM enrolls


WHERE studentID = s.studentID


HAVING COUNT( * ) > 2




)



 

2)

The following example selects students not enrolled in any courses:



 


SELECT studentID, studentName


FROM students s


WHERE EXISTS (




SELECT COUNT( * )


FROM enrolls


WHERE studentID = s.studentID


HAVING COUNT( * ) = 0




)



 

 


 

Conformance

 

SQL:2003 standard

-

-

Core SQL

Feature T501 "Enhanced EXISTS predicate"