We’ve already talked about some of the features that are new in MySQL 4.1, such as prepared statements and multiple statements and the SHOW WARNINGS command, in previous chapters. In this section we’ll discuss what are probably the two most important new additions in MySQL 4.1 because of the increased speed, flexibility, and power they lend to SELECT queries. These are subqueries (also known as nested queries or subselects) and derived tables. In addition, as we’ll see later in this chapter, they mark an important stage in MySQL’s evolution, as they pave the way for enterprise-level features such as stored procedures, triggers, and views.

Subqueries and Derived Tables

Try as we might to combine queries using the various types of joins, SQL functions, and operators that we’ve looked at previously in this book, sometimes it’s necessary to use more than one query to derive the exact data that we require. Even in cases where we can use a single query to obtain a desired result, we find that what seems conceptually very simple often requires very complex joins or unions. In MySQL 4.1, these problems can often be overcome through the use of subqueries, that is, queries within other queries. Being able to use subqueries can ease matters greatly, and they can allow us to combine queries or simplify them considerably. They also provide for structured queries, in which the different parts of a query can be considered apart from the others. This tends to make them much more easily read and understood than the more complex statements that they can be used to replace.

Subqueries can be used in several places within queries and in several ways. In the next few examples, we’ll use two tables, representing products and categories of products. (You may recognize some of the products table data from the QA Testing example in Chapter 2.) These tables are structured as shown in the following two CREATE TABLE statements:

CREATE TABLE products (
  id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_id INT(11) NOT NULL,
  name VARCHAR(30) NOT NULL,
  price DECIMAL(6,2) NOT NULL
);
CREATE TABLE categories (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

Each product has a name and a price. Each product belongs to a category (and only one category); this relationship is indicated by the category_id column in the products table, which serves as a foreign key linking to the categories table.

The table creation statements and some statements to insert sample data are included in the ch8 directory of the code download for this book (available from the Downloads section of http://www.apress.com ).

Subqueries can be used in any of the SELECT , INSERT , UPDATE , DELETE , or SET statements. (They can also be used with the DO statement, which we’ll see when we discuss stored procedures later in this chapter.) Subqueries can include any of the constructs found in any other SELECT query, including FROM and WHERE clauses, joins, LIMIT clauses, unions, function calls, and so forth.