HomeMySQL Page 3 - What’s New In MySQL 4.1 Part One: Overview and Subqueries
Derived Tables - MySQL
The current release of MySQL, version 4.1.10, offers significant improvements over version 4. While it still has some room for improvement, its new features and capabilities should silence the critics who have up until now regarded it as little more than a toy. In this article, the first of two parts, David Fells covers scalar and correlated queries, derived tables, and row level subqueries.
The next type of subquery uses what is referred to as a derived table, appearing in the list above as “a table to select from.” In this usage, the subquery expression appears as a part of the FROM clause or to test membership. Subqueries used to test membership will be used with keywords like ANY, EXISTS, and NOT EXISTS in the outer query and may or may not be replaceable by a join. On the flip side are tables to select from, which are easily replaced with joins in nearly any situation. The tables created by these subqueries are called derived tables, which are essentially unnamed, non-persistent views.
Testing membership is one of the core features that any implementation of SQL should provide. SQL is a set oriented language and memberhip tests are fundamental in set theory. While MySQL has provided the UNION operation for as long as I can remember, the EXISTS, NOT EXISTS, and ANY operations are new and much needed. Subqueries that test membership can also be correlated and are especially useful when performing complex reporting operations. In the following example, we will select values from one table where they do not exist in another.
SELECT column1 FROM table 1 WHERE NOT EXISTS ( SELECT column1 FROM table2 )
In this example, the inner query is executed first, selecting column1 from table2. The result of that subquery is used in the NOT EXISTS clause which, logically, is the same as using NOT IN and declaring a set of values by hand. In fact, the EXISTS and IN statements can be used interchangeably in this context.
An example of a derived table in the FROM clause is shown below.
SELECT column1 FROM ( SELECT column1 FROM table2 )
The process here is similar to that of a correlated subquery – the engine first processes the inner query, creating a derived table. That derived table is then used in the outer query as the target for record selection. These queries can also be correlated and are typically more useful in that context, but do have uses on their own.