SQL 中的 ON、WHERE 和 HAVING

概述

1. 之前忽视的一个问题：一直以为 SQL 中的 JOIN 操作可以直接使用 WHERE 来设置连接条件。但实际上并不能这样用，只有在 SQL89 中表连接是使用 WHERE 设置连接条件的，而在 SQL92 中引入的 JOIN 表连接语法必须使用 ON 或 USING 来设置连接条件。

2. 虽然 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。

1. 查询每个学生的姓名及其及格课程的数量(以 60 分为及格线)
2. 查询学生姓名为 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 后的一组数据进行过滤。