Exists Predicate

>>-EXISTS-(fullselect)-----------------------------------------><

The EXISTS predicate tests for the existence of certain rows. The fullselect can specify any number of columns, and:
• The result is true only if the number of rows that is specified by the fullselect is not zero.
• The result is false only if the number of rows specified by the fullselect is zero.
• The result cannot be unknown.
The SELECT clause in the fullselect can specify any number of columns because the values returned by the fullselect are ignored.
For convenience, use:
SELECT *

Unlike the NULL, LIKE, and IN predicates, the EXISTS predicate has no form that contains the word NOT. To negate an EXISTS predicate, precede it with the logical operator NOT, as follows:
NOT EXISTS (fullselect)
The result is then false if the EXISTS predicate is true, and true if the predicate is false. Here, NOT is a logical operator and not a part of the predicate. Logical operators are discussed in Search conditions.

Example 1: The following query lists the employee number of everyone represented in DSN8810.EMP who works in a department where at least one employee has a salary less than 20000. Like many EXISTS predicates, the one in this query involves a correlated variable.
SELECT EMPNO FROM DSN8810.EMP X WHERE EXISTS (SELECT * FROM DSN8810.EMP
WHERE X.WORKDEPT=WORKDEPT AND SALARY<20000);
Example 2: List the subscribers (SNO) in the state of California who made at least one call during the first quarter of 2000. Order the results according to SNO. Each MONTHnn table has columns for SNO, CHARGES, and DATE. The CUST table has columns for SNO and STATE.

SELECT C.SNO FROM CUST C WHERE C.STATE = 'CA' AND EXISTS (SELECT * FROM MONTH1
WHERE DATE BETWEEN '01/01/2000 AND '01/31/2000' AND C.SNO = SNO UNION ALL
SELECT * FROM MONTH2 WHERE DATE BETWEEN '02/01/2000 AND '02/29/2000'
AND C.SNO = SNO UNION ALL SELECT * FROM MONTH3 WHERE DATE BETWEEN '03/01/2000 AND '03/31/2000' AND C.SNO = SNO) ORDER BY C.SNO;

Notes :
A caveat is that, if we use this exists on a large set of data, the same case of huge amount of memory being dedicated to the locklist (an update lock is placed on row till it is committed) occurs and can slow down the system.

A code snippet from a netting SP in production.

update ccdb.inst_claim_dtl set ccdb.inst_claim_dtl.last_update_nm = 'NETTING',
ccdb.inst_claim_dtl.error_cd = (select SESSION.DETAILS.error_cd
from SESSION.DETAILS where ccdb.inst_claim_dtl.client_cd = '534' and SESSION.DETAILS.claim_type_cd = '01' and ccdb.inst_claim_dtl.claim_id = SESSION.DETAILS.claim_id and ccdb.inst_claim_dtl.seq_num = SESSION.DETAILS.seq_num) where exists (select 1 from SESSION.DETAILS where ccdb.inst_claim_dtl.client_cd = '534' and SESSION.DETAILS.claim_type_cd = '01' and ccdb.inst_claim_dtl.claim_id = SESSION.DETAILS.claim_id and ccdb.inst_claim_dtl.seq_num = SESSION.DETAILS.seq_num);

Back to Index