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
(2) Explanation of specification format
(3) Evaluation of the predicate
(4) Rules
(5) Example

(1) Specification format

EXISTS-predicate ::= EXISTS table-subquery

To Page Top

(2) Explanation of specification format

table-subquery:: For details about table subqueries, see 7.3 Subqueries.

To Page Top

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

Table 7‒3: 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`

To Page Top

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

To Page Top

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

To Page Top