How to solve "subquery returns more than 1 row" error

Question

I have a query that returns multiple rows, and another query where I want to set the condition to be any one of these multiple row values, so basically I want the subquery to look like this: select*fromtablewhereid=(multiple row query) ;The multi-row query returns multiple rows. So if the values ​​of these rows are 1, 2, 3, then I want the id to be set to 1 or 2 or 3.

P粉310754094 · Answer

You can use in():

select * 
from table
where id in (多行查询)

Or use connection:

select distinct t.* 
from source_of_id_table s
join table t on t.id = s.t_id
where

Connections are never a worse choice in terms of performance, and depending on the circumstances and the database you are using, may provide better performance.

P粉386318086 · Answer

= can be used when a subquery returns only one value.

When a subquery returns multiple values, you need to use IN:

select * 
from table
where id IN (multiple row query);

For example:

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students)   --子查询只返回一个值

SELECT *
FROM Students
WHERE Marks IN 
      (SELECT Marks 
       FROM Students 
       ORDER BY Marks DESC
       LIMIT 10)                       --子查询返回10个值