Java Reference
In-Depth Information
Item_Number
Name
Description
Qty
Cost
1003
Shredded Wheat
Cereal
103
2.05
1006
Fig Bar
Cookies
162
1.57
Using the IN and NOT IN operators
As you recall from the section on operators earlier in this chapter, the IN predicate is
used to compare values against a list. For example, to return all customers in either
NY or NJ, you can use the IN predicate to check the customer's state against a list
containing 'NY' and 'NJ'. Here's an example:
SELECT * FROM CUSTOMERS
WHERE STATE IN ('NY','NJ');
Also, you can use the IN predicate with a subquery to populate the list. The following
code snippet uses a subquery to create a list of item numbers from the Orderd_Items
Table and uses the IN predicate to return the corresponding inventory data:
SELECT *
FROM INVENTORY
WHERE Item_Number IN
(SELECT Item_Number
FROM Ordered_Items
WHERE Order_Number = 2);
The result set this query returns looks like this:
Item_Number
Name
Description
Qty
Cost
1001
Corn Flakes
Cereal
178
1.95
1004
Oatmeal
Cereal
15
0.98
1005
Chocolate Chip
Cookies
217
1.26
1010
Orange
Soda
84
0.71
In addition, you can use the IN predicate with the NOT operator to select all inventory
items that are not included in the select list. Note that you can only specify one
SELECT list item when using the IN predicate, since the list is returned for
comparison with a single item.
Using the EXISTS and NOT EXISTS predicates
