一个子查询返回值多于一个的错误

创建一个学生，教师，以及记录多对多关系的学生教师关系表：

create table teacher
(
id varchar(40) primary key,
name varchar(20)
);

create table student
(
id varchar(40) primary key,
name varchar(20)
);
create table teacher_student
(
t_id varchar(40),
s_id varchar(40),
primary key(t_id,s_id),
constraint t_id_FK foreign key (t_id) references teacher(id),
constraint s_id_FK foreign key (s_id) references student(id)
);

在查找教师实体时遇到一个错误，查方法的源码是：

public Teacher find_t(String id) throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils_C3P0.getDs());
String sql = "select * from teacher where id=?";
Teacher t =  (Teacher) qr.query(sql, id, new BeanHandler(Teacher.class));

//根据外键关系表，取出学生的信息
//这个sql语句是错误的额，子查询返回的值多余一个，
sql = "select * from student where id=(select s_id from teacher_student where t_id=?)";
List<Student> list = (List<Student>) qr.query(sql, t.getId(), new BeanListHandler(Student.class));
t.getSet().addAll(list);

return t;
}

运行结果报错为：

java.sql.SQLException: Subquery returns more than 1 row Query: select * from student where id=(select s_id from teacher_student where t_id=?) Parameters: [1]

因为教师可以对应着多个学生，所以子查询返回的值自然可以多于一个，而当子查询跟随在=、~=、<、<=、>、>=之后或子查询用作表达式时，这种情况是不允许的。

所以sql语句应该使用连接查询：sql="select * from student s,teacher_student t_s where s.id=t_s.s_id and t_s.t_id=?";