Home arrow MySQL arrow 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.

TABLE OF CONTENTS:
  1. What’s New In MySQL 4.1 Part One: Overview and Subqueries
  2. Scalar and Correlated Subqueries
  3. Derived Tables
  4. Row Level Subquery
By: David Fells
Rating: starstarstarstarstar / 47
March 14, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By David Fells
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: