Subqueries

Understanding Subqueries

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.

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;