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`);
Follow us on Twitter
Subscribe to RSS Feed
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!
a faster version
SELECT `col1` FROM `table1`
INNER JOIN (SELECT `col2` FROM `table2` LIMIT 1); as table2
ON table1.`col1` table2.col2
reagrds
gj.