MariaDB Crash Course - Ben Forta [40]
Input
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001,10004);
Instead of hard-coding those customer IDs, you can turn this WHERE clause into yet another subquery:
Input
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 = 'TNT2'));
Output
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
Analysis
To execute this SELECT statement, MariaDB had to actually perform three SELECT statements. The innermost subquery returned a list of order numbers that were then used as the WHERE clause for the subquery above it. That subquery returned a list of customer IDs that were used as the WHERE clause for the top-level query. The top-level query actually returned the desired data.
As you can see, using subqueries in a WHERE clause enables you to write powerful and flexible SQL statements. There is no limit imposed on the number of subqueries that can be nested, although in practice you will find that performance tells you when you are nesting too deeply.
* * *
Caution: Columns Must Match
When using a subquery in a WHERE clause (as seen here), make sure that the SELECT statement has the same number of columns as in the WHERE clause. Usually, a single column will be returned by the subquery and matched against a single column, but multiple columns may be used if needed.
* * *
Although usually used in conjunction with the IN operator, subqueries can also be used to test for equality (using =), nonequality (using <>), and so on.
* * *
Caution: Subqueries and Performance
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, although it might be. More on this is in Chapter 15, “Joining Tables,” where you revisit this same example.
* * *
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.
As you learned in the previous two chapters, you can use SELECT COUNT(*) to count rows in a table, and by providing a WHERE clause to filter a specific customer ID, you can count just that customer’s orders. For example, the following code counts the number of orders placed by customer 10001:
Input
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;
To perform that COUNT(*) calculation for each customer, use COUNT* as a subquery. Look at the following code:
Input
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
Output
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
Analysis
This SELECT statement returns three columns for every customer in the customers table: cust_name, cust_state, and orders. orders is a calculated field that is set by a subquery provided in parentheses. That subquery is executed once for every customer retrieved. In this example, the subquery is executed five times because five customers were retrieved.
The WHERE clause in the subquery is a little different