SQL 方便的子查询及EXISTS

本节要点

使用子查询作为计算字段
使用子查询过滤数据（IN）
使用子查询过滤数据（EXISTS）

使用子查询作为计算字段

假如有一张老师表teacher(teacher_id,teacher_name,gender)和一张学生表student(student_id,student_name,gender,birth_day,age,class_id,score,teacher_id)，考虑一下下面这个查询需求：

（1）、如何同时查询出学生编号、学生姓名、老师编号、老师姓名？

因为学生表中只有老师编号，没有老师姓名，而老师姓名保存在另一个老师表中。那这个查询需求，就需要用到两张表。

对于这个查询需求，就可以使用到我们这节课讲解的子查询。先来看下这个查询需求，SQL语句应该怎么写：

SELECT
student_id,
student_name,
teacher_id,
(
    SELECT teacher_name
    FROM teacher
    WHERE teacher.teacher_id = student.teacher_id
)
FROM student

在这个SQL语句里，有两个SELECT，也就是说，在一个SELECT语句里嵌套了另一个SELECT语句。这就是子查询，是子查询作为SELECT的一个计算字段的写法。

在子查询的内部，使用了student表名来引用外部student表的字段，使用teacher表名来引用teacher表的字段。

其实，我们可以简化一些，为这两张表各取一个别名，那么在使用的时候，就可以使用表别名来引用表中的字段。就像下面这个SQL：

SELECT
student_id,
student_name,
teacher_id,
(
    SELECT teacher_name
    FROM teacher b
    WHERE b.teacher_id = a.teacher_id
)
FROM student a;

这个SQL语句与上面的SQL语句是等价的。

这里同时使用到了student表中的teacher_id字段和teacher表中的teacher_id字段，为了明确指定到底是哪个表的teacher_id字段，所以使用了表别名。

其实，如果在SQL语句中，对于使用的字段没有歧义，也就是说，没有同时使用到两个表的相同字段，表别名也是可以省略的。

另外，我们假设一下，如果teacher表中，相同的teacher_id具有多条记录，那么对于同一个学生表中的teacher_id的值，可能会在子查询中查到多条记录，这时候应该返回哪一条呢？

实际上，如果发生这样的情况，执行的时候是会报错的：SQL执行错误 #1242 从数据库的响应 Subquery returns more than 1 row。

使用子查询过滤数据（IN）

再考虑下这个查询需求：

（1）、如何获取姓牛的老师教了哪些学生？

一般来说，可能需要分两步来实现。

第1步：从老师表中查出所有姓牛的老师。

SELECT
    teacher_id,teacher_name
FROM teacher
WHERE teacher_name like '牛%’;

第2步：使用IN操作符，将姓牛老师的编号列出来，然后从学生表中查出需要的数据。

SELECT
    student_id,student_name
FROM student
WHERE teacher_id IN ('T0010','T0011');

一般情况下，分两步来写，也能得到我们想要的结果。可是也存在两个问题。

问题一：如果姓牛的老师太多（比如超过了500人），如果将所有老师的编号都列出来，那SQL语句就太长了；

问题二：这样写，也需要两步来实现我们的查询需求，实现起来太复杂了；

那么，可不可以使用一句简单的SQL，就搞定这个查询需求呢？

实际上，我们知道，在IN操作符后面的括号里面，是明确列出了一个姓牛老师的老师编号的集合，而上面的第１步的SELECT语句，也是返回了一个相同的集合。

那么，也就可以使用SELECT语句替换掉IN操作符后面括号里的内容，像下面这样：

SELECT
    student_id,student_name
FROM student
WHERE teacher_id IN ( SELECT teacher_id FROM teacher
    WHERE teacher_name like '牛%'
);

这是使用子查询配合IN操作符来过滤数据的一种写法。

是不是比前面的两步操作要简单的多了。

使用子查询过滤数据（EXISTS）

我们还可以使用子查询配合EXISTS关键字来实现上面的需求：

SELECT
    student_id,student_name
FROM student a
WHERE EXISTS ( SELECT 1 FROM teacher b
    WHERE a.teacher_id = b.teacher_id
    AND b.teacher_name like '牛%'
);

EXISTS是存在的意思。从WHERE后面的子查询来看，也就是说，存在一个学生的老师编号，在老师表里，对应的老师姓牛。

那既然有存在的写法，当然也有不存在的写法。

（1）、如何获取除姓牛的老师之外的其他老师教了哪些学生？

SELECT
    student_id,student_name
FROM student a
WHERE NOT EXISTS ( SELECT 1 FROM teacher b
    WHERE a.teacher_id = b.teacher_id
    AND b.teacher_name like '牛%'
);

写起来也很简单，就是在EXISTS前面加上了一个NOT，用来表示否定后面的条件。