SQL中on和where的使用及其差异
浏览数：23 /
时间：2015年06月12日
在编写SQL脚本中，多表连接查询操作需要使用到on和where条件，但是经常会混淆两者的用法，从而造成取数错误。为此，使用测试数据进行总结如下：
1、现有两张测试表，table_a和table_b。
table_a表中的数据：14条记录，主键是(user_id，brand_id)，是用户在每个月份访问的上网品牌及其访问频率。
table_b表中的数据：主键是user_id，是用户基本类别表，Type_Id是用户在该月份的用户类别。
（1）通过user_id和month_id连接起来的效果：
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 ;
最终的记录数等于主表的记录数。
主表的选择很重要，这里的记录数等于主表记录数，但不是总是这样的，与非主表中的记录组成也有关系。
（2）添加where条件的效果：
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 WHERE A1.Brand_Id=‘501B03‘ 13 ;
说明where条件是对连接完成产生的查询结果的再次筛选。会影响到最后的记录数。
（3）继续添加where条件的效果：
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 WHERE A1.Brand_Id=‘501B03‘ 13 AND A2.Type_Id=10 14 ;
进一步说明，where条件是对连接产生的查询结果集的二次筛选。
（4）添加更多个on连接条件的效果：
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 AND A1.Brand_Id=‘501B03‘ 13 ;
总结如下：这里是对主表添加了On条件，它会对主表参与多表连接的记录进行一次过滤，只有满足这个条件的主表记录才会参与与其他表的连接操作，获取其他表中的字段组成一条需要的记录；主表中不符合这个on条件的记录也会进入到最后的结果表中，但不会参与与其他表的连接，因此，记录中其他表中获取的字段处全是NULL。
这里可以体现出，主表的特殊性，在left join 里面，主表就是多表连接最左边的那张表，表里面所有的记录都会出现在最后的结果集中，但是只有满足on条件的记录才会参与与其他表的连接操作。
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 AND A2.Type_Id=10 13 ;
总结如下：这里添加了对被连接表的On条件，这个条件也不会影响主表在最后结果集中的记录，它的作用主要是对被连接表首先进行一次筛选，它会让符合这个条件的被连接表中的记录去参与与主表的连接操作，其他不符合on条件的记录根本就没有机会参与与主表的连接。
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 AND A1.Brand_Id=‘501B03‘ 13 AND A2.Type_Id=10 14 ;
这个表就综合了上面两方面，使用Brand_Id=‘501B03‘对主表进行一次筛选，让符合这一条件的记录参与与其他表的连接，其他记录不参与连接，但是仍进入结果表（空位字段补NULL）;使用Type_Id=10对被连接表A2首先做一次筛选，让符合这一条件的记录参与与主表A1的连接，不符合这一条件的记录被剔除掉不予考虑，也没有机会参与连接和进入结果表。
（5）更改table_b的主键，将其设为（user_id,month_id），并添加一条记录如下：
这样再table_b中对应user_id=989832就有两条记录。
执行下面的SQL：
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 ;
这样可以看到，最后查询结果集的记录数不再是14条记录，而是15条，这说明了一点，最后的结果集中的记录数并不是和主表中记录数一致的，而是由主表和被连接表根据连接条件共同确定的，还可以说明的一点是，不论on条件是什么样，多简单多复杂，只要没有where条件，最后的查询结果集中的记录数不会少于主表记录数。对于这个例子，主表中的14条记录都会进入结果表，由于主表中的连接条件只有User_Id相等，因此，对于A2表中的User_Id=989832由于存在两条记录，因此都会与A1表User_Id=989832的那一条记录相连，因此产生了两条连接结果记录，所以使得最后的结果集增至15条记录。
1 SELECT A1.User_Id 2 ,A1.Month_Id 3 ,A1.Brand_Id 4 ,A1.Rate 5 ,A2.User_Id 6 ,A2.Month_Id 7 ,A2.Type_Id 8 FROM table_a A1 9 LEFT JOIN table_b A2 10 ON A1.User_Id=A2.User_Id 11 AND A1.Month_Id=A2.Month_Id 12 ;
上面给A1和A2表的连接条件增加了一个Month_Id相等的条件，这样只有满足这一条件的A2表记录才会参与连接，这样就对A2表中的User_Id=989832剔除了一条记录，所以最后连接只产生了14条记录。
2、综上所述，予以总结如下：
（1）ON条件，不论是A1.COL1=A2.COL2还是A2.COL3=XX，都是对A2表（被连接表）进行的条件筛选，将符合这一条件的记录取出来，参与与主表的连接操作，不符合该条件的记录都会被考虑参与连接。对于条件A1.COL4=XXX，其作用是对A1表（主表）进行条件筛选，符合该条件的记录将会参与与其他表的连接，不符合该条件的记录也会进入最后的结果表，空位补NULL。而A1.COL1=A2.COL2其实也是对A1表的筛选，让满足条件的A1表中的记录参与与其他表的连接，不满足的空位补NULL继续进入结果表。不论怎么使用ON条件，只要没有WHERE条件筛选，最后查询的结果集的记录数都是大于等于主表原始的记录数。
（2）WHERE条件，是对查询完毕后的结果集进行的筛选。可使用的筛选条件字段为任意的，因为多表查询实质是多个表之间的全字段连接查询，只是可以指定最后对外显示多少个字段的记录。where条件会从根本上影响最后查询结果集的记录数。
（3）如果对连接查询后聚集函数结果进行的筛选则需要使用GROUP BY +HAVING搭配完成。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
