如何在单个SQL查询中验证用户归属后返回班级全部学生

现有如下数据库表结构及测试数据：

CREATE TABLE children (
  id int,
  name varchar(255)
);

CREATE TABLE classrooms (
  id int,
  name varchar(255)
);

CREATE TABLE childrenToClassrooms (
  childId int,
  classRoomId int 
);

INSERT INTO children (id, name) VALUES (1, 'Mike');
INSERT INTO children (id, name) VALUES (2, 'Jake');
INSERT INTO children (id, name) VALUES (3, 'Arnold');
INSERT INTO children (id, name) VALUES (4, 'Alan');

INSERT INTO classrooms (id, name) VALUES (1, 'class1');

INSERT INTO childrenToClassrooms (childId, classRoomId) VALUES (1, 1);
INSERT INTO childrenToClassrooms (childId, classRoomId) VALUES (2, 1);
INSERT INTO childrenToClassrooms (childId, classRoomId) VALUES (3, 1);

需求：仅当发起请求的学生（例如childId=1）属于目标班级时，才返回该班级的所有学生，能否通过单个SQL查询实现？

当前尝试的SQL语句：

SELECT children.name FROM children
INNER JOIN childrenToClassrooms ON childrenToClassrooms.childId = children.id AND classrooms.id = 1
-- IF childrenToClassrooms.childId = 1 exists ?

可以通过单个SQL实现，核心是用EXISTS子查询验证请求学生的班级归属，只有验证通过时才返回目标班级的所有学生。

以请求学生childId=1、目标班级classRoomId=1为例，最终SQL如下：

SELECT c.name
FROM children c
JOIN childrenToClassrooms cc ON c.id = cc.childId
WHERE cc.classRoomId = 1
  AND EXISTS (
    SELECT 1
    FROM childrenToClassrooms
    WHERE childId = 1 AND classRoomId = 1
  );

逻辑说明 #

1. EXISTS子查询先校验请求学生是否在目标班级中：
   • 若存在，外层查询会返回该班级的所有学生（Mike、Jake、Arnold）；
   • 若不存在（比如换成childId=4，或删除该学生的班级关联记录），外层查询返回空结果集。
2. 外层查询通过关联childrenToClassrooms筛选出目标班级的学生，结合EXISTS条件实现权限控制。

如果需要动态替换请求学生ID或目标班级ID，修改EXISTS子查询和外层WHERE条件中的对应值即可。

内容的提问来源于stack exchange，提问作者RDU