7.19.2 EXISTS predicate
The EXISTS predicate is used to determine whether a table subquery result is zero rows (the empty set).
- Organization of this subsection
(1) Specification format
EXISTS-predicate ::= EXISTS table-subquery
(2) Explanation of specification format
- table-subquery:
-
For details about table subqueries, see 7.3 Subqueries.
(3) Evaluation of the predicate
If the table subquery returns one or more rows, the result of the EXISTS predicate is TRUE. If the table subquery returns zero rows (the empty set), result of the EXISTS predicate is FALSE. The following table shows the results of the EXISTS predicate.
No. |
Number of rows in the table subquery results |
Result of the EXISTS predicate |
---|---|---|
1 |
One or more rows |
TRUE |
2 |
Zero rows (the empty set) |
FALSE |
(4) Rules
If * or table-specification.* is specified in the selection list in a table subquery, it makes sense to specify a column in the table specified by the table reference in the table subquery. On the other hand, it is not appropriate to specify a set function here.
(5) Example
- Example
-
From the sales history table (SALESLIST) and product list table (PRODUCTSLIST), this example retrieves information on products for which there have been sales.
SELECT * FROM "PRODUCTSLIST" WHERE EXISTS(SELECT * FROM "SALESLIST" WHERE "SALESLIST"."PUR-CODE"="PRODUCTSLIST"."PUR-CODE")
The underlined portion indicates the EXISTS predicate.