What’s New In MySQL 4.1 Part One: Overview and Subqueries - Row Level Subquery
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 47 
