Database Reference
In-Depth Information
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 have the IDs of all the customers who ordered item
TNT2
. The next
step is to retrieve the customer information for each of those customer IDs.
The SQL statement to retrieve the two columns is
