mysql 外链接 后面的on_MySQL外连接中ON条件对数据结果集的筛选

在进行外连接时，对于表数据的筛选条件可以使用AND添加在ON之后，也可以放在WHERE子句之中，本文根据筛选条件放置位置的不同在mysql中的作用范围进行讨论。

1 表结构

如下使用两种信息表，一个是人员表person，包括id，name，age等信息，该表所有字段信息都不为空，另一个表hobby，使用id与person表的id关联，其中p_hobby代表人的爱好，可以为空。数据如下所示。

(1)人员信息表

id name age

1 zwh 27

2 zfy 28

3 zxy 26

4 ysf 21

5 rjp 21

(2)爱好表

id p_hobby

1 reading,writing

2 learning, eating

3 sleepint

4 NULL

5 NULL

2 左外连接Person左外连接Hobby

1>外连接的效果

使用左外连接进行该文话题的探讨，如下，在不适用任何限定条件的情况下用Person左外连接hobby，结果如下所示：

SELECT *

FROM PERSON

LEFT JOIN HOBBY ON PERSON.id=HOBBY.id;

2>ON中的条件限制与WHERE中的条件限制(视情况参考第三部分)

如果有个条件：hobby表中p_hobby字段为空的不能进行连接操作，即p_hobby IS NOT NULL条件。将筛选条件使用AND放在ON的后面，SQL为：

SELECT *

FROM person

LEFT JOIN hobby ON person.id=hobby.id AND hobby.p_hobby IS NOT NULL;

结果为：

将筛选条件放在WHERE子句中，SQL为：

SELECT *

FROM person

LEFT JOIN hobby ON person.id=hobby.id

WHERE hobby.p_hobby IS NOT NULL;

结果为：

如图所示结果集1中与不加限定的不同在于hobby.id在ON后有筛选条件的子句时，id为null，说明在进行外连接前先进行了p_hobby IS NOT NULL的筛选，得到的临时结果集如下所示：

然后在将该结果集与person表做LEFT JOIN操作才能得到如上图中的第一个结果集，相当于该SQL的操作：SELECT * FROM person LEFT JOIN (SELECT * FROM hobby WHERE p_hobby IS NOT NULL) AS tmp ON person.id=tmp.id;

第二个结果集明显是在person表和hobby表进行LEFT JOIN操作过后，然后使用条件进行结果集的过滤。

由此得出结论，将筛选条件放置在ON中是先对表进行过滤，然后使用过滤后的结果集进行外连接操作；将筛选条件放置在WHERE中是先对字表进行外连接操作，然后在结果集中进行条件筛选。

3 ON条件的作用总结

作为单独使用条件的话， ON 中有三个条件，其意义分别为匹配连接条件，选择左表数据，选择右表数据等，如下：

1>单独条件——表示连接的条件a.id=b.id

两个表的连接条件，只有 a.id 与 b.id 相同的记录才进行匹配，SQL为SELECT * FROM person LEFT JOIN hobby ON person.id=hobby.id; 结果如下图：

2>单独条件——对左表数据筛选

a.id=1或a.id>3等条件， 符合这个条件的记录被另一个表匹配，SQL为：SELECT * FROM person LEFT JOIN hobby ON person.id=1; 对应结果为：

SQL为：SELECT * FROM person LEFT JOIN hobby ON person.id<=2；(SELECT * FROM person LEFT JOIN hobby ON person.id=1 OR person.id=2;效果相同)对应结果为：

由以上例子可见，如果ON后面没有匹配条件，单单存在对左表的限制条件，那么选出的所有的数据都要与右表的全部数据进行匹配。

3>单独条件——对右表数筛选

如果在ON后面单独对右表的数据进行限制，SQL为SELECT * FROM person LEFT JOIN hobby ON hobby.id<=2 OR hobby.id =5;

如上图可见，单独对右表做限制，是将从右表中筛选出的每一条数据，去强制匹配左表的每一条数据。

4> 复合条件——连接条件+对左表的数据筛选

在连接条件后对左表进行限制，SQL为：SELECT * FROM person LEFT JOIN hobby ON person.id=hobby.id AND (person.id<=2 OR person.id=5); 结果如下图所示：

上图可见结果集中保留了左表中的所有数据，但是只有满足条件的坐标的数据集参与了匹配，不满足条件的数据集虽然出现在了结果集中，但是其对应的右表全部为NULL。

5>复合条件——连接条件+对右表的数据筛选

在连接条件后对右表的限制中，SQL为：SELECT * FROM person LEFT JOIN hobby ON person.id=hobby.id AND (hobby.id=3); 结果如下图所示：

上图可见ON条件中对于右表的过滤是只有符合条件的数据集才参与匹配。

6>复合条件——对左表筛选+对右表筛选

在对左表和右表的数据筛选，SQL为SELECT * FROM person LEFT JOIN hobby ON person.id=2 AND (hobby.id=1 OR hobby.id=2); 结果如下图所示：