Java Reference
In-Depth Information
SELECT Name, Cost,
(SELECT AVG(Cost) FROM Inventory WHERE Description = 'soda') AS Average
FROM Inventory WHERE Description = 'soda';
Notice how the entire subquery replaces the column name, so that the AS clause
used to name the column appears outside the parentheses defining the subquery.
The results of this query look like this:
Name
Cost
Average
Cola
0.61
0.63
Lemon
0.57
0.63
Orange
0.71
0.63
Using a subquery with the INSERT command
You can use subqueries in the INSERT command just as easily as you can in a
SELECT command. Consider an example where you might want to insert selected
records from one table into another. One way to do this is to use a subquery to select
the desired subset from the source table.
In the following example, a subquery is used to select the Customer_Numbers of
customers from New Jersey. Then the appropriate fields are selected from customers
with the selected Customer_Numbers, and inserted into the Employees Table:
INSERT INTO Employees (Employee_ID, First_Name, Last_Name)
SELECT Customer_Number, First_Name, Last_Name
FROM Customers
WHERE Customer_Number IN
(SELECT Customer_Number
FROM Customers
WHERE State = 'NJ');
Using a subquery with the UPDATE command
A more common usage of a subquery is with the UPDATE command. This example
uses a subquery to select the Customer_Number of the customer to be updated from
the Customers Table. You then use this customer number in the WHERE clause of
the UPDATE command as shown here:
UPDATE Employees
SET First_Name = 'Alfie'
WHERE Employee_ID IN
(SELECT Customer_Number
