|
<< Click to Display Table of Contents >> EXISTS predicate |
  
|
|
<< Click to Display Table of Contents >> EXISTS predicate |
|
Indicates whether values exist in a subquery.
EXISTS subquery
Use the EXISTS comparison predicate to filter a table based on the existence of column values from the table in a subquery. The subquery is filtered using a WHERE clause comparing one or more columns in the filtered table to corresponding columns in the subquery. EXISTS returns a true value if the subquery has at least one row in its result set, false if zero rows are retrieved. The subquery is executed once for each row in the filtered table and the existence of rows in the subquery is used to include or exclude the rows in the filtered table.
SELECT O.OrderNo, O.CustNo
FROM Orders O
WHERE EXISTS
(SELECT C.CustNo
FROM Customer C
WHERE (C.CustNo = O.CustNo))
The subquery may be further filtered with other conditions. For example, the statement below returns the rows pertaining to all customers who have placed Orders the totals for which exceed $1000.
SELECT C.Company, C.CustNo
FROM Customer C
WHERE EXISTS
(SELECT O.CustNo
FROM Orders O
WHERE (O.CustNo = C.CustNo) AND
(O.ItemsTotal > 1000))
Use NOT to return the converse of an EXISTS comparison.
