MySQL数据库，内连接、外连接中做条件筛选和WHERE中条件筛选的区别

最近写数据库SQL做连接、条件筛选数据操作时发现了一个问题，就是两张表在做等值连接、或者外连接时用AND对连接表数据进行筛选和最后用WHERE对结果集数据表进行筛选得到的查询结果是有区别的。

先来个简单例子看下，在对结果进行具体分析。

1.外连接

ON后用AND对连接表数据进行筛选

WITH table_1 AS(
	SELECT 'a1' AS id_1, 'b1' AS id_2, 'name_1' AS name_1
	UNION ALL
	SELECT 'a2' AS id_1, 'b2' AS id_2, 'name_2' AS name_1
	UNION ALL
	SELECT 'a3' AS id_1, 'b3' AS id_2, 'name_3' AS name_1
	UNION ALL
	SELECT 'a4' AS id_1, 'b1' AS id_2, 'name_4' AS name_1
),
table_2 AS(
	SELECT 'b1' AS id_2, 'aa' AS name_2
	UNION ALL
	SELECT 'b2' AS id_2, 'bb' AS name_2
)

SELECT
	*
FROM
	table_1
LEFT OUTER JOIN table_2
ON table_1.id_2 = table_2.id_2
AND table_2.name_2 = 'aa'

结果

修改成WHERE对结果数据集筛选

WITH table_1 AS(
	SELECT 'a1' AS id_1, 'b1' AS id_2, 'name_1' AS name_1
	UNION ALL
	SELECT 'a2' AS id_1, 'b2' AS id_2, 'name_2' AS name_1
	UNION ALL
	SELECT 'a3' AS id_1, 'b3' AS id_2, 'name_3' AS name_1
	UNION ALL
	SELECT 'a4' AS id_1, 'b1' AS id_2, 'name_4' AS name_1
),
table_2 AS(
	SELECT 'b1' AS id_2, 'aa' AS name_2
	UNION ALL
	SELECT 'b2' AS id_2, 'bb' AS name_2
)

SELECT
	*
FROM
	table_1
LEFT OUTER JOIN table_2
ON table_1.id_2 = table_2.id_2
WHERE table_2.name_2 = 'aa'

结果

2.等值连接

ON后用AND对连接表数据进行筛选

WITH table_1 AS(
	SELECT 'a1' AS id_1, 'b1' AS id_2, 'name_1' AS name_1
	UNION ALL
	SELECT 'a2' AS id_1, 'b2' AS id_2, 'name_2' AS name_1
	UNION ALL
	SELECT 'a3' AS id_1, 'b3' AS id_2, 'name_3' AS name_1
	UNION ALL
	SELECT 'a4' AS id_1, 'b1' AS id_2, 'name_4' AS name_1
),
table_2 AS(
	SELECT 'b1' AS id_2, 'aa' AS name_2
	UNION ALL
	SELECT 'b2' AS id_2, 'bb' AS name_2
)

SELECT
	*
FROM
	table_1
INNER JOIN table_2
ON table_1.id_2 = table_2.id_2
AND table_2.name_2 = 'aa'

结果

修改成WHERE对结果数据集筛选

WITH table_1 AS(
	SELECT 'a1' AS id_1, 'b1' AS id_2, 'name_1' AS name_1
	UNION ALL
	SELECT 'a2' AS id_1, 'b2' AS id_2, 'name_2' AS name_1
	UNION ALL
	SELECT 'a3' AS id_1, 'b3' AS id_2, 'name_3' AS name_1
	UNION ALL
	SELECT 'a4' AS id_1, 'b1' AS id_2, 'name_4' AS name_1
),
table_2 AS(
	SELECT 'b1' AS id_2, 'aa' AS name_2
	UNION ALL
	SELECT 'b2' AS id_2, 'bb' AS name_2
)

SELECT
	*
FROM
	table_1
INNER JOIN table_2
ON table_1.id_2 = table_2.id_2
WHERE table_2.name_2 = 'aa'

结果

原理：在外连接ON后用AND做条件筛选数据时，是先对连接表的数据进行条件筛选，筛选后和被连接表进行相应连接，而等值连接、WHERE是对结果集进行筛选，先连接后筛选数据。

其实以上结果的关键原因就是 left join、right join、full join 的特殊性，
不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。
而 inner jion 没这个特殊性，则条件放在 on 中或 where 中，返回的结果集是相同的。

**A inner join B 取交集，条件放在 on 中或 where 中，返回的结果集是相同的。
A left join B 取 A 全部， on条件筛选 B，没有对应的值为 null。
A right join B 取 B 全部，on条件筛选 A， 没有对应的值为 null。
A full outer join B 取并集，对应条件在 on 后面填写，彼此没有对应的值为 null，显示两表中全部记录。
**