HomeMySQL Page 4 - What’s New In MySQL 4.1 Part One: Overview and Subqueries
Row Level Subquery - 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 final type of subquery to discuss is a row level subquery. Row level subqueries return a single row and may contain any number of columns. They use what are called row constructors. The following two statements demonstrate the two available row constructor syntaxes used in row subqueries.
SELECT * FROM table1 WHERE (1, 2) = (SELECT column1, column2 FROM table2); SELECT * FROM table1 WHERE ROW(1, 2) = (SELECT column1, column2 FROM table2);
The expressions (1, 2) and ROW(1, 2) are equivalent, each creating a logical row for evaluation against the subquery. The following example demonstrates the use of a row subquery to find a row match on three columns in another table.
SELECT column1, column2, column3 FROM table1 WHERE (column1, column2, column3) IN ( SELECT column1, column2, column3 FROM table2 )
This example is equivalent to:
SELECT table1.column1, table1.column2, table1.column3 FROM table1, table2 WHERE table1.column1 = table2.column1 AND table1.column2 = table2.column2 AND table1.column3 = table2.column3
The subquery uses a membership test with the IN statement in the WHERE clause, whereas the join simply uses multiple conditions in the WHERE clause. Logically, the first example makes more sense, as we are not selecting any rows from table2 – however, the second example will be the faster performer, because MySQL is extremely good at join optimization and not so great at optimizing set operations.
In this article we have been introduced to MySQL 4.1 and gone over the syntax for using the various types of subqueries that are now available. Subqueries are extremely useful tools but are often supurfluous and can be easily replaced by a much more efficient table join. There are of course an equal number of situations where subqueries cannot be simulated with any number of joins. In these cases, subqueries are invaluable. I hope that having been introduced to the syntax, you all will get your hands dirty and experiment with them. In the next article, we will go over all the other changes, including new character set handling, date handling, and a lot of other smaller changes.