Subqueries are queries that are embedded into other queries.
Filtering by Subquery
Subqueries allows us to create individual queries that can be joined together to return the required information. An example would to a get a list of all the customers who ordered item RGAN01. This information is contained in separate tables.
To get the information we would do the following :
- Retrieve the order numbers of all orders containing item RGAN01
- Retrieve the customer ID of all the customers who have orders listed in the order numbers returned in the previous step.
- Retrieve the customer information for all the customer IDs returned in the previous step.
Retrieve the matching order numbers
SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';
Retrieve the customer id of the customers who ordered the item
SELECT cust_id FROM Orders WHERE order_num IN (20007,20008);
We can get this information with query that uses subqueries to combine the individual queries as follows :
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');
Subqueries are always processed starting with the innermost SELECT statement and working outward.
Finally we can run the following to return the results :
SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));
Subquery SELECT statements can only retrieve a single column. Attempting to retrieve multiple columns will return an error.
Performance Implications of Subqueries
The code shown here works, and it achieves the desired result. However, using subqueries is not always the most efficient way to perform this type of data retrieval.
Using Subqueries as Calculated Fields
Another way to use subqueries is in creating calculated fields. Suppose you want to display the total number of orders placed by every customer in your Customers table. Orders are stored in the Orders table along with the appropriate customer ID. To perform this operation, follow these steps: 1. Retrieve the list of customers from the Customers table. 2. For each customer retrieved, count the number of associated orders in the Orders table.
SELECT COUNT(*) AS orders FROM Orders WHERE cust_id = '1000000001';
To get the count we can do :
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
You will need to use fully qualified names in order to return the proper results, otherwise you will get incorrect results :
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders FROM Customers ORDER BY cust_name;