O'Reilly logo

Upgrading to PHP 5 by Adam Trachtenberg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required