SQL中join后的on和where的过滤条件的区别
1、原理
1.1连接基本概念
（1）内连接（inner join、join）：必须完全满足连接运算（natural、using、on）的左右表数据行。
（2）左外连接（left outer join、left join）：仅保留左表在连接运算之前的数据行，无匹配的右表列填充null。
（3）右外连接（right outer join、right join）：仅保留右表在连接运算之前的数据，无匹配的左表列填充null。
（4）全外连接（full outer join、full join）：保留左右表在连接运算之前的数据，无匹配的左表或右表列填充null。
（5）交叉连接(cross join)：没有on或者using，因此无需讨论。
1.2说明
（1）内连接中，on过滤条件可以被where过滤条件取代，反之亦然。在on后边写连接条件where后边写其余条件的优点是可读性好。
（2）外连接中，on过滤条件和where过滤条件不同，外连接运算将不符合on过滤条件的行当作空值行存在，也就是行存在把列值抹去。
2、验证
2.1创建表
分别对左外连接中左表和右表上的ON和WHERE过滤条件进行验证。
首先创建两张表和示例数据，简单起见没有创建索引和外键：
CREATE TABLE books (
id char(2) PRIMARY KEY,
title varchar(20) NOT NULL,
primary_author_id char(2) NOT NULL -- 关联authors.id
);
CREATE TABLE authors (
id char(2) PRIMARY KEY,
fullname varchar(20) NOT NULL
);
INSERT INTO books VALUES
('B0', '三国演义', 'A0'),
('B1', '货币战争', 'A1'),
('B2', '水浒传', 'A2');
INSERT INTO authors VALUES
('A0', '罗贯中'),
('A2', '吴承恩'),
('A3', '金庸');
2.2books表on过滤条件
SELECT a.title, b.fullname
FROM books a
LEFT JOIN authors b
ON a.primary_author_id = b.id
AND a.title = '三国演义';
查询计划1（EXPLAIN (analyze, costs off, timing off, summary off) 后边跟随SQL语句）：
Hash Left Join (actual rows=3 loops=1)
Hash Cond: (a.primary_author_id = b.id)
Join Filter: ((a.title)::text = '三国演义'::text)
Rows Removed by Join Filter: 1
-> Seq Scan on books a (actual rows=3 loops=1)
-> Hash (actual rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on authors b (actual rows=3 loops=1)
查询结果1：
title | fullname
----------+----------
三国演义 | 罗贯中
货币战争 |
水浒传 |
2.3books表WHERE过滤条件
SELECT a.title, b.fullname
FROM books a
LEFT JOIN authors b
ON a.primary_author_id = b.id
WHERE a.title = '三国演义';
查询计划2：
Hash Right Join (actual rows=1 loops=1)
Hash Cond: (b.id = a.primary_author_id)
-> Seq Scan on authors b (actual rows=3 loops=1)
-> Hash (actual rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on books a (actual rows=1 loops=1)
Filter: ((title)::text = '三国演义'::text)
Rows Removed by Filter: 2
查询结果2：
title | fullname
----------+----------
三国演义 | 罗贯中
2.4authors表ON过滤条件
SELECT a.title, b.fullname
FROM books a
LEFT JOIN authors b
ON a.primary_author_id = b.id
AND b.fullname = '罗贯中';
查询计划3：
Hash Left Join (actual rows=3 loops=1)
Hash Cond: (a.primary_author_id = b.id)
-> Seq Scan on books a (actual rows=3 loops=1)
-> Hash (actual rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on authors b (actual rows=1 loops=1)
Filter: ((fullname)::text = '罗贯中'::text)
Rows Removed by Filter: 2
查询结果3：
title | fullname
----------+----------
三国演义 | 罗贯中
货币战争 |
水浒传 |
2.5authors表WHERE过滤条件
SELECT a.title, b.fullname
FROM books a
LEFT JOIN authors b
ON a.primary_author_id = b.id
WHERE b.fullname = '罗贯中';
查询计划4：
Hash Join (actual rows=1 loops=1)
Hash Cond: (a.primary_author_id = b.id)
-> Seq Scan on books a (actual rows=3 loops=1)
-> Hash (actual rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on authors b (actual rows=1 loops=1)
Filter: ((fullname)::text = '罗贯中'::text)
Rows Removed by Filter: 2
查询结果4：
title | fullname
----------+----------
三国演义 | 罗贯中
