MySQL Error Resolved – Subquery Returns More Than 1 Row

July 26th, 2012 - Posted by Steve Marks to MySQL, Web Development.

If you’ve found this post you’re probably trying to find out why your MySQL query is returning the following error:

Subquery returns more than 1 row

To allow me to explain the problem let me show you an example of a query that might throw this kind of error:

SELECT `col1` FROM `table1`
WHERE `col1` = (SELECT `col2` FROM `table2`);

In the query above you’ll get the error if the secondary query after the WHERE clause return two or more rows.

The Solution

To solve this, depending on your requirement, you have one of two options:

Limit the query

SELECT `col1` FROM `table1`
WHERE `col1` = (SELECT `col2` FROM `table2` LIMIT 1);

Match against the entire resultset

SELECT `col1` FROM `table1`
WHERE `col1` IN (SELECT `col2` FROM `table2`);

Or:

SELECT `col1` FROM `table1`
WHERE `col1` = ANY (SELECT `col2` FROM `table2`);
This entry was posted on Thursday, July 26th, 2012 at 7:14 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (3)
  1. Em Comments says:

    Thanks, really clear and useful. Solved an hours fruitless searching.

  2. Maksim says:

    Thanks for the explanation! Though be careful with using constructions like:
    SELECT `col1` FROM `table1`
    WHERE `col1` IN (SELECT `col2` FROM `table2`);
    OR
    SELECT `col1` FROM `table1`
    WHERE `col1` = ANY (SELECT `col2` FROM `table2`);
    Because of existing issue in MySQL (http://bugs.mysql.com/bug.php?id=32665), for details read (http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql). The problem is due to a missing optimization when a subquery using IN (and “= ANY”) is incorrectly identified as dependent subquery instead of an independent subquery. The result is slow performance.

    Thanks again!

  3. gjergj says:

    a faster version
    SELECT `col1` FROM `table1`
    INNER JOIN (SELECT `col2` FROM `table2` LIMIT 1); as table2
    ON table1.`col1` table2.col2

    reagrds
    gj.