SQL 中的 ON、WHERE 和 HAVING
概述
-
之前忽视的一个问题：一直以为 SQL 中的
JOIN操作可以直接使用WHERE来设置连接条件。但实际上并不能这样用，只有在 SQL89 中表连接是使用WHERE设置连接条件的，而在 SQL92 中引入的JOIN表连接语法必须使用ON或USING来设置连接条件。 -
虽然
ON(USING)、WHERE和HAVING都可以用于设置查询条件，但是它们作用是不同的。
基础知识
使用 SQL89 进行连表查询:
SELECT t1.id, t1.name, t2.date
FROM tb1 t1, tb2 t2
WHERE t1.id = t2.tb1_ref_id;
使用 SQL92 进行连表查询:
SELECT t1.id, t1.name, t2.date
FROM tb1 t1
LEFT JOIN tb2 t2
ON t1.id = t2.tb1_ref_id;
在 SQL 中 ON 是用于设置多表连接时的连接条件的，不符合连表条件的数据不会生成对应的连接记录；而使用 WHERE 设置的过滤条件是作用于连表完成之后的连接记录的，因此当连表后的记录不符合过滤条件时，只有该条连接记录不会出现在查询结果中；对于使用 HAVING 设置的过滤条件是针对 GROUP BY 分组后的每组记录的，因此通常需要使用聚合函数进行过滤。
示例
假设现在有两张数据表：学生基本信息(id, stu_name)，学生成绩表(id, stu_id, course, score)
CREATE TABLE studenttb (
id bigint PRIMARY KEY,
stu_name varchar(50)
);
CREATE TABLE scoretb (
id bigint PRIMARY KEY,
stu_id bigint,
course varchar(50),
score integer
);
INSERT INTO studenttb (id, stu_name)
VALUES (1, 'foo'), (2, 'bar');
INSERT INTO scoretb (id, stu_id, course, score)
VALUES(1, 1, 'chinese ', 98), (2, 1, 'math', 96), (3, 1, 'english', 95), (4, 2, 'chinese ', 59), (5, 2, 'math', 42), (6, 2, 'english', 40);
请写出满足以下需求的 SQL。
- 查询每个学生的姓名及其及格课程的数量(以 60 分为及格线)
- 查询学生姓名为 bar 的平均成绩
情况一
当第一题中错误的使用 WHERE 代替 ON 时：
SELECT s.stu_name, count(c.score) pass_num
FROM studenttb s
LEFT JOIN scoretb c
ON s.id = c.stu_id
WHERE c.score >= 60
GROUP BY s.id, s.stu_name;
查询结果：
stu_name pass_num
foo 3
这里将 score >= 60 的条件放在了 WHERE 中导致查询结果中学生姓名为 bar 的数据丢失(因为该学生的及格门数为 0)，正确的 SQL 应该是：
SELECT s.stu_name, count(c.score) pass_num
FROM studenttb s
LEFT JOIN scoretb c
ON s.id = c.stu_id AND c.score >= 60
GROUP BY s.id, s.stu_name;
查询结果：
stu_name pass_num
foo 3
bar 0
情况二
当第二题中错误的使用 ON 代替 WHERE 时：
SELECT s.stu_name, avg(c.score) avg_score
FROM studenttb s
LEFT JOIN scoretb c
ON s.id = c.stu_id AND s.stu_name = 'bar'
GROUP BY s.id, s.stu_name;
查询结果：
stu_name avg_score
foo NULL
bar 47.0000
在这个反例中，使用了 ON 进行对学生姓名的过滤，但实际上因为 LEFT JOIN 的特性 stu_name 不等于 'bar' 的结果仍然会出现在查询结果中，只是它们将会与一条 NULL 记录进行连接，因此查询结果中出现了 "foo NULL" 的记录，正确的 SQL 应该为：
SELECT s.stu_name, avg(c.score) avg_score
FROM studenttb s
LEFT JOIN scoretb c
ON s.id = c.stu_id
WHERE s.stu_name = 'bar'
GROUP BY s.id, s.stu_name;
查询结果：
stu_name avg_score
bar 47.0000
总结
在 LEFT JOIN 中当需要在连表前对记录进行限制时需要使用 ON 设置连表条件，而当需要在连表后进行过滤需要使用 WHERE 进行操作，HAVING 和 WHERE 同理，但是它们的不同之处在于 WHERE 是针对 GROUP BY 后的聚合字段进行条件过滤，而 HAVING 是针对 GROUP BY 后的一组数据进行过滤。
