Before and After: Subselects
Subselects are a popular database feature thatâs available in MySQL 4.1. A subselect, or a subquery, is a query that occurs within the context of another query. You then use the subselectâs results in the main query.
Many developers like subselects because they allow them to chain queries together to winnow results. Itâs often possible to rewrite a query to eliminate a subselect; however, this is not always straightforward or efficient. Additionally, sometimes, without a subselect, youâll be forced to make multiple requests.
The following sections contain a few examples that show how a
subselect can solve problems. Many of them use the following
programs
table:
mysql> DESCRIBE programs; +------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------+------+-----+---------+----------------+ | id | int(5) unsigned | | PRI | NULL | auto_increment | | title | varchar(50) | | | | | | channel_id | int(5) unsigned | | | 0 | | +------------+-----------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM programs; +----+-----------------+------------+ | id | title | channel_id | +----+-----------------+------------+ | 1 | Oprah | 60 | | 2 | Sex and the City| 201 | | 3 | The Sopranos | 201 | | 4 | Frontline | 13 | +----+-----------------+------------+ 4 rows in set (0.00 sec)
MySQL 4.0: Finding Related Entries
A common database-related ...
Get Upgrading to PHP 5 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.