MySQL (ERROR Subquery returns more than 1 row) - Yet I am expecting more than 1 row

Question

I am expecting multiple rows:

SELECT * FROM OWNER 

    WHERE OWNER.ID IN(
            (
            SELECT 
                ID 
            FROM OWNER 
            WHERE 
                LAST_NAME LIKE '%pickles%'
                OR FIRST_NAME LIKE '%pickles%'
            ),

            (
            SELECT 
                OWNER_ID 
            FROM VISITOR 
            WHERE 
                NAME LIKE '%pickles%'
                OR SIZE LIKE '%pickles%'
            ),

            (
            SELECT 
                OWNER_ID 
            FROM BOARDING 
            WHERE 
                COMMENTS LIKE '%pickles%'
                OR PERSONAL_BELONGINGS LIKE '%pickles%'
            )

        )

The second Subquery

FROM VISITOR 
        WHERE 
            NAME LIKE '%pickles%'

Is causing the error. Yet I am expecting multiple rows returned? It should be returning a number of ID's from the rows found. So why does it have a problem with these multiple rows?

Thank you.

Answer 1

The accepted answer does offer a working solution, but an incorrect explanation of why the existing query doesn't work as written.

There is more than one type of subquery, returning more than one type of result.

In your query:

WHERE expr IN (( /* subq 1 */ ), ( /* subq 2 */ ), ( /* subq 3 */ )) 

...is equivalent to...

WHERE expr = ( /* subq 1 */ )
   OR expr = ( /* subq 2 */ )
   OR expr = ( /* subq 3 */ )

This is, and can only be, three equality comparisons. Subqueries used this way would necessarily be interpreted in scalar context... and a scalar subquery can return only one row, or zero rows, because the result of the subquery will be used as a scalar operand. For the server to interpret it otherwise is illogical: can you compare a single value to two values or more, with an equality comparison? No.

Your query is not, as it might seem, asking the server to build the set of all the results from the subqueries, and test whether OWNER.ID is IN() that set. Your query is asking the server to build a set containing a maximum three scalar values, one from each subquery, and identify whether OWNER.ID is IN() that (probably much smaller) set. The fact that any one of those subqueries returns more than one row makes the query invalid. You're fortunate that it already doesn't work. Had you chosen data that matched only one row, it would have worked now, but broken down the road, when you had more matches.

ERROR 1242 (21000): Subquery returns more than 1 row

If you (artificially) added LIMIT 1 to each of the three subqueries, you'd find that the query actually does work, and this illustrates my point. The result is going to be incomplete, but the query is valid, which disproves the assertion that the error is about "columns." It's a set... but, as written, it is, and can only be, a set of scalars.

Refactoring with the UNION clarifies your intention to the server.

Note that if the assertion that the error is about columns were correct, the message would be different:

ERROR 1241 (21000): Operand should contain 1 column(s)

Now, having explained the actual reason for the error, I'll also throw in an alternate solution. You should have an index on (OWNER_ID) in both VISITOR and BOARDING, if you haven't already. Performance between this solution and the UNION solution is likely to vary depending on the makeup of the data in the various tables as well as the version of MySQL Server. Neither solution is optimal, because a beginning % in a LIKE comparison requires either the table, or at least one index containing the column (if there is one) to be read in its entirety. This is a full table scan or a full index scan, and "scan" means row-by-row. The ending % is fine; the beginning % is costly because it un-anchors the pattern from the left hand side of the column, which is where indexes do their work. Remove it if you can, wherever your application will permit, for better performance, and index the searched columns; if you can't remove the leading %, at least understand that this is a contributor to the performance you will see at scale. This is a limitation of B-Tree indexes in general, not MySQL specifically. You may also eventually want to look into FULLTEXT search.

SELECT o.* 
  FROM OWNER o
 WHERE o.LAST_NAME LIKE '%pickles%'
    OR o.FIRST_NAME LIKE '%pickles%'
    OR EXISTS(SELECT * 
                FROM VISITOR v 
               WHERE v.OWNER_ID = o.ID 
                 AND (v.NAME LIKE '%pickles%' OR v.SIZE LIKE '%pickles%')
             )
    OR EXISTS(SELECT *
                FROM BOARDING b 
               WHERE b.OWNER_ID = o.ID 
                 AND (COMMENTS LIKE '%pickles%' OR PERSONAL_BELONGINGS LIKE '%pickles%')
             );

Note that EXISTS(SELECT * ...) is yet another form of subquery, entirely different than anything we've considered so far. Particularly of note is the fact that the * is only a convention. You can say SELECT 1 or SELECT TRUE and it doesn't matter, because no data is actually being selected, and nothing is being returned by EXISTS(), other than TRUE -- the subquery matches one or more rows in the target table -- or FALSE -- it doesn't. Note that these queries reference a value in the outer query, o.ID, so they

Here, we're checking the owner table for matching criteria, and then, if necessary, we're checking whether rows exist in either VISITOR or BOARDING for matching criteria. If a woman named "Cat" has a dog named "Cat" and you're searching for "Cat" then we'll find it in OWNER and the optimizer may be able to avoid the search of the other two tables for that user, since we already know she's a match, for whichever reason might be the case.

See also: https://dev.mysql.com/doc/refman/5.6/en/subquery-errors.html

Answer 2

I think you are looking for an union:

SELECT * FROM OWNER 
WHERE OWNER.ID IN(

        SELECT ID 
        FROM OWNER 
        WHERE LAST_NAME LIKE '%pickles%'
            OR FIRST_NAME LIKE '%pickles%'

        UNION

        SELECT OWNER_ID 
        FROM VISITOR 
        WHERE NAME LIKE '%pickles%'
            OR SIZE LIKE '%pickles%'

        UNION

        SELECT OWNER_ID 
        FROM BOARDING 
        WHERE COMMENTS LIKE '%pickles%'
            OR PERSONAL_BELONGINGS LIKE '%pickles%'


    )

Explanation:

Your subquery returns the result in 3 columns, like:

ID                     OWNER_ID               OWNER_ID1
-----------------------------------------------------------------
OWNER_Table_ids        Visitor_Table_ids      Boarding_Table_ids

Using UNION, it will return like:

ID   
-------------------
OWNER_table_ids
Visitor_table_ids
Boarding_table_ids

The error "Subquery returns more than 1 value" simply means "more than 1 column".