Building a Subquery

Simply put, a subquery lets you tie the result set of one query to another. The general syntax is as follows:

SELECT *
FROM TABLE1
WHERE TABLE1.SOMECOLUMN =
(SELECT SOMEOTHERCOLUMN
FROM TABLE2
WHERE SOMEOTHERCOLUMN = SOMEVALUE)

Notice how the second query is nested inside the first. Here's a real-world example that uses the PART and ORDERS tables:

						SELECT *
						FROM PART PARTNUM DESCRIPTION PRICE =========== ==================== =========== 54 PEDALS 54.25 42 SEATS 24.50 46 TIRES 15.25 23 MOUNTAIN BIKE 350.45 76 ROAD BIKE 530.00 10 TANDEM 1200.00 mysql> select * from part; +---------+---------------+---------+ | partnum | description | price | +---------+---------------+---------+ | 54 | PEDALS | 54.25 | | 42 | SEATS | 24.50 ...

Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.