Row Level Subquery - MySQL

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.