Database Reference
In-Depth Information
▼
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 sub-
query 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 pow-
erful 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 perfor-
mance 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.
