Java Reference
In-Depth Information
EXISTS and NOT EXISTS are predicates. That is, they return true or false. They are
used in true/false comparisons to determine whether the subquery returns any
records. For example, you can use a subquery to return a result set of Ordered Items
matched up by Order Number and Customer Number to the customer who has
ordered them. Then you can find out what kinds of cookies he or she has ordered
using the comparison Description = 'Cookies' with the EXISTS predicate, as shown
here:
SELECT DISTINCT Name
FROM Inventory
WHERE Description = 'Cookies' AND EXISTS
(SELECT *
FROM Customers c, Ordered_Items oi, Orders o, Inventory i
WHERE c.Customer_Number = o.Customer_Number AND
oi.Order_Number = o.Order_Number AND
oi.Item_Number = i.Item_Number);
This query returns this result set:
Name
Chocolate Chip
Fig Bar
Sugar Cookies
Notice the use of the asterisk (*) as the SELECT list for the subquery. Conventionally,
you use an asterisk with the EXISTS predicate because EXISTS only returns true or
false, so there is nothing to be gained by being more specific.
Note
The EXISTS predicate stops the search as soon as it finds a single match
and is therefore much faster and more efficient than a query that
continues to check for additional rows that match.
Correlated subqueries
As a rule, the main FROM list should only contain tables that are referenced in the
main SELECT. In this case, the main SELECT clause includes only inventory. You
can also use table name aliases in a subquery to refer to tables listed in a FROM
clause outside the subquery, as in the following example. This usage is known as a
correlated subquery:
SELECT c.First_Name, c.Last_Name, o.Order_Number, i.Item_Number, i.Name
FROM Customers c, Inventory i, Orders o
WHERE i.Description = 'Cookies' AND EXISTS
Search WWH ::




Custom Search