SQL中on和where的使用及其差异

在编写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搭配完成。
 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。