Java Reference
In-Depth Information
SET First_Name = 'Alfie'
WHERE Employee_ID IN
(SELECT Customer_Number
FROM Customers
WHERE First_Name = 'Sonny');
Using a Subquery with the DELETE Command
Finally, here's an example of the use of a subquery with the
DELETE
command. This example uses a
subquery to select the Customer_Numbers of all the customers with a Last_Name of Corleone. This
list of Customer_Numbers is used in the
DELETE
command to identify the customers to be deleted
from the Customers table. In this instance, you will get a list of all Corleones in the Customer table,
regardless of whether they are employees. You then use this customer number list in the
WHERE
clause of the
DELETE
command and delete any employees in the list.
DELETE FROM Employees
WHERE Employee_ID IN
(SELECT Customer_Number
FROM Customers
WHERE Last_Name = 'Corleone');
Correlated Subqueries
Most of the subqueries discussed so far are self-contained, in that they refer only to tables defined
within the subquery itself. This self-contained aspect of subqueries has the advantage of making them
easy to check out as stand-alone queries. However, sometimes it's useful to use outside references in
a subquery.
Correlated subqueries
are subqueries that depend on a value in the outer query. A reference to a table
in the outer query is called a
correlated reference
. The following example presents a correlated query
in the reference to the Customers table. In the following code, Customers appears in the
FROM
clause
of the outer query, but not in the
FROM
clause of the subquery:
SELECT c.First_Name, c.Last_Name, i.Name, i.Item_Number
FROM Customers c, Inventory i
WHERE c.Last_Name = 'Corleone' AND
i.Description = 'Cookies' AND EXISTS
(SELECT *
FROM Ordered_Items oi, Orders o
WHERE c.Customer_Number = o.Customer_Number AND
oi.Order_Number = o.Order_Number AND
oi.Item_Number = i.Item_Number);
