MySQL Error Resolved – Subquery Returns More Than 1 Row

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:

1. SELECT `col1` FROM `table1`  
2. 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

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

Match against the entire resultset

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

Or:

1. SELECT `col1` FROM `table1`  
2. WHERE `col1` = ANY (SELECT `col2` FROM `table2`);