EXISTS and NOT EXISTS
The EXISTS keyword tests the existence of specific rows in the result of a subquery. The NOT EXISTS keyword tests for the nonexistence of specific rows in the result of a subquery.
Syntax (EXISTS)
<source-expression>
WHERE EXISTS <subquery>
Syntax (NOT EXISTS)
<source-expression>
WHERE NOT EXISTS <subquery>
Remarks
• EXISTS checks for the existence of rows under conditions specified in the subquery; the actual values in the rows are irrelevant. Therefore, the SELECT clause in the subquery is SELECT * to retrieve all columns.
• The subquery can return any number of rows and columns.
• The subquery returns at least one row if the EXISTS condition is met and the NOT EXISTS condition is false.
• If the subquery does not return any rows, the EXISTS condition is not met and the NOT EXISTS condition is true.
• Even if the rows returned by the subquery contain NULL values, they are not ignored. Such rows are considered normal rows.
Example (EXISTS)
SELECT *
FROM /shared/examples/ds_inventory/suppliers
WHERE EXISTS (SELECT *
FROM /shared/examples/ds_inventory/purchaseorders
WHERE purchaseorders.SupplierID = 5)
Example (NOT EXISTS)
SELECT *
FROM /shared/examples/ds_inventory/suppliers
WHERE NOT EXISTS (SELECT *
FROM /shared/examples/ds_inventory/purchaseorders
WHERE purchaseorders.SupplierID = 100)