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 a value for any one of these multiple rows, so basically I want the subquery to look like this:

select *
from table
where id= (multi-row query);

Where 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.

Answer 1

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 <source_of_id_table的条件>

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.

Answer 2

= 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个值