Java Reference
In-Depth Information
Figure 7-1:
Subquery using ALL
Note
When creating a subquery, the entire subquery should be enclosed in parentheses.
Using the IN and NOT IN Predicates
The
IN
predicate is used to retrieve the records in the main query that have a matching record in the
data set returned by the subquery. This usage is similar to the simple
SELECT
used with an
IN
list, as
shown here:
SELECT * FROM CUSTOMERS
WHERE STATE IN ('NY','NJ');
In this example, the query returns all Customers where the State field is listed in the parenthesized
IN
list, or, in other words, where the State field equals either NY or NJ.
The example shown in
Figure 7-2
returns all items from the Inventory table whose item numbers can
be found in the Ordered_Items table, with Order_Number = 2.
Figure 7-2:
Subquery using IN
The
NOT IN
predicate, of course, reverses the selection.
Note
You can only specify one
SELECT
list item when using the
IN
predicate, since the list is
returned for comparison with a single item.
