Upgrading to PHP 5

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 ...