数据库表模型
假设我们的数据库中存在以下两个表,它们形成了一对多的表关系。
student
表是父表,student_grade
是子表,因为它有一个 student_id 外键列引用 student 表的 id 主键列。
student
表包含以下两条记录:
id |
first_name |
last_name |
admission_score |
1 |
Alice |
Smith |
8.95 |
2 |
Bob |
Johnson |
8.75 |
student_grade
表存储学生获得的成绩:
id |
class_name |
grade |
student_id |
1 |
Math |
10 |
1 |
2 |
Math |
9.5 |
1 |
3 |
Math |
9.75 |
1 |
4 |
Science |
9.5 |
1 |
5 |
Science |
9 |
1 |
6 |
Science |
9.25 |
1 |
7 |
Math |
8.5 |
2 |
8 |
Math |
9.5 |
2 |
9 |
Math |
9 |
2 |
10 |
Science |
10 |
2 |
11 |
Science |
9.4 |
2 |
SQL EXISTS
假设我们想要获取所有在数学课程中获得10分的学生。
如果我们只关心学生的标识符,那么我们可以运行以下查询语句:
SELECT
student_grade.student_id
FROM
student_grade
WHERE
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
ORDER BY
student_grade.student_id
然而,该应用程序希望显示学生
的全名,而不仅仅是标识符,因此我们还需要从学生
表中获取信息。
为了过滤在数学成绩上获得10分的学生
记录,我们可以使用EXISTS SQL运算符,如下所示:
SELECT
id, first_name, last_name
FROM
student
WHERE EXISTS (
SELECT 1
FROM
student_grade
WHERE
student_grade.student_id = student.id AND
student_grade.grade = 10 AND
student_grade.class_name = 'Math'
)
ORDER BY id
运行以上查询时,我们可以看到仅选择了Alice行:
id first_name last_name
1 Alice Smith
外部查询选择了我们想要返回给客户端的
student
行列。然而,WHERE子句使用了EXISTS运算符和相关联的内部子查询。
如果子查询返回至少一条记录,则EXISTS运算符返回true,否则返回false。数据库引擎并不必须完全运行子查询。如果匹配到一条记录,则EXISTS运算符返回true,并选择相关联的其他查询行。
内部子查询是相关联的,因为
student_grade
表的
student_id
列与外部
student
表的
id
列进行了匹配。
SQL NOT EXISTS
假设我们希望选择所有成绩不低于9分的学生。为此,我们可以使用NOT EXISTS运算符,该运算符否定了EXISTS运算符的逻辑。
因此,如果底层子查询没有返回任何记录,则NOT EXISTS运算符返回true。但是,如果内部子查询匹配到一条记录,则NOT EXISTS运算符将返回false,并且可以停止子查询执行。
为了匹配所有没有与小于9分关联的
student_grade
的学生记录,我们可以运行以下SQL查询:
SELECT
id, first_name, last_name
FROM
student
WHERE NOT EXISTS (
SELECT 1
FROM
student_grade
WHERE
student_grade.student_id = student.id AND
student_grade.grade < 9
)
ORDER BY id
运行上述查询时,我们可以看到只有 Alice 的记录被匹配:
id |
first_name |
last_name |
1 |
Alice |
Smith |
因此,使用 SQL EXISTS 和 NOT EXISTS 操作符的优点在于,只要找到匹配的记录,内部子查询的执行就会停止。