MariaDB Crash Course - Ben Forta [39]
14. Working with Subqueries
In this chapter, you learn what subqueries are and how to use them.
Understanding Subqueries
SELECT statements are SQL queries. All the SELECT statements you have seen thus far are simple queries: single statements retrieving data from individual database tables.
* * *
New Term: Query
Any SQL statement. However, the term is usually used to refer to SELECT statements.
* * *
SQL also enables you to create subqueries: queries that are embedded into other queries. Why would you want to do this? The best way to understand this concept is to look at a couple of examples.
Filtering by Subquery
The database tables used in all the chapters in this book are relational tables. (See Appendix B, “The Example Tables,” for a description of each of the tables and their relationships.) Order data is stored in two tables. The orders table stores a single row for each order containing order number, customer ID, and order date. The individual order items are stored in the related orderitems table. The orders table does not store customer information. It only stores a customer ID. The actual customer information is stored in the customers table.
Now suppose you wanted a list of all the customers who ordered item TNT2. What would you have to do to retrieve this information? Here are the steps:
1. Retrieve the order numbers of all orders containing item TNT2.
2. Retrieve the customer ID of all the customers who have orders listed in the order numbers returned in the previous step.
3. Retrieve the customer information for all the customer IDs returned in the previous step.
Each of these steps can be executed as a separate query. By doing so, you use the results returned by one SELECT statement to populate the WHERE clause of the next SELECT statement.
You can also use subqueries to combine all three queries into one single statement.
The first SELECT statement should be self-explanatory by now. It retrieves the order_num column for all order items with a prod_id of TNT2. The output lists the two orders containing this item:
Input
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
Output
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
The next step is to retrieve the customer IDs associated with orders 20005 and 20007. Using the IN clause described in Chapter 7, “Advanced Data Filtering,” you can create a SELECT statement as follows:
Input
SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);
Output
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
Now, combine the two queries by turning the first (the one that returned the order numbers) into a subquery. Look at the following SELECT statement:
Input
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2');
Output
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
Analysis
Subqueries are always processed starting with the innermost SELECT statement and working outward. When the preceding SELECT statement is processed, MariaDB actually performs two operations.
First it runs the subquery:
SELECT order_num FROM orderitems WHERE prod_id='TNT2'
That query returns the two order numbers 20005 and 20007. Those two values are then passed to the WHERE clause of the outer query in the comma-delimited format required by the IN operator. The outer query now becomes
SELECT cust_id FROM orders WHERE order_num IN (20005,20007)
As you can see, the output is correct and exactly the same as the output returned by the previous hard-coded WHERE clause.
* * *
Tip: Formatting Your SQL
SELECT statements containing subqueries can be difficult to read and debug, especially as they grow in complexity. Breaking up the queries over multiple lines and indenting the lines appropriately as shown here can greatly simplify working with subqueries.
* * *
You now