Java Reference
In-Depth Information
Using the EXISTS and NOT EXISTS Predicates
The
EXISTS
and
NOT EXISTS
predicates are used in true/false comparisons to determine whether the
subquery returns any records. You will use
EXISTS
in a subquery to find out what kinds of cookies
have been ordered by members of the Corleone family.
The main query shown in
Figure 7-3
selects the first and last names of the family member, and the
name of the preferred type of cookie for all instances of a cookie preference returned by executing an
EXISTS
subquery on the tables.
Figure 7-3:
Subquery using EXISTS
Note
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.
As a rule, the main
FROM
list should only contain tables that are referenced in the main
SELECT
statement. In this case, you listed Customers and Inventory in the main
SELECT
statement and used
their aliases in combination with the Orders and Ordered_Items tables in the subquery.
The
EXISTS
statement stops the search as soon as it finds a single match. The
EXIST
statement is
therefore much faster and more efficient than a query that continues to check for additional rows that
match.
Cross-Reference
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 example in
Figure 7-3
. This
capability, known as a correlated subquery, is discussed later in this chapter
Nesting Subqueries
Just as you can use a subquery within a query, you can also use a subquery within another subquery.
Subqueries can be nested as deeply as your implementation of SQL allows. The syntax for nesting
subqueries looks like this:
SELECT *
FROM Tables
