Database Reference
In-Depth Information
14
In this chapter, you learn what subqueries are and how to use them.
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.
The database tables used in all the chapters in this topic 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.