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连接起来的效果：

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
;

最终的记录数等于主表的记录数。

主表的选择很重要，这里的记录数等于主表记录数，但不是总是这样的，与非主表中的记录组成也有关系。

（2）添加where条件的效果：

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
WHERE A1.Brand_Id='501B03'
;

说明where条件是对连接完成产生的查询结果的再次筛选。会影响到最后的记录数。

（3）继续添加where条件的效果：

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
WHERE A1.Brand_Id='501B03'
AND A2.Type_Id=10
;

 

进一步说明，where条件是对连接产生的查询结果集的二次筛选。

（4）添加更多个on连接条件的效果：

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
AND A1.Brand_Id='501B03'
;

总结如下：这里是对主表添加了On条件，它会对主表参与多表连接的记录进行一次过滤，只有满足这个条件的主表记录才会参与与其他表的连接操作，获取其他表中的字段组成一条需要的记录；主表中不符合这个on条件的记录也会进入到最后的结果表中，但不会参与与其他表的连接，因此，记录中其他表中获取的字段处全是NULL。

这里可以体现出，主表的特殊性，在left join 里面，主表就是多表连接最左边的那张表，表里面所有的记录都会出现在最后的结果集中，但是只有满足on条件的记录才会参与与其他表的连接操作。

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
AND A2.Type_Id=10
;

 

总结如下：这里添加了对被连接表的On条件，这个条件也不会影响主表在最后结果集中的记录，它的作用主要是对被连接表首先进行一次筛选，它会让符合这个条件的被连接表中的记录去参与与主表的连接操作，其他不符合on条件的记录根本就没有机会参与与主表的连接。

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
AND A1.Brand_Id='501B03'
AND A2.Type_Id=10
;

这个表就综合了上面两方面，使用Brand_Id='501B03'对主表进行一次筛选，让符合这一条件的记录参与与其他表的连接，其他记录不参与连接，但是仍进入结果表（空位字段补NULL）;使用Type_Id=10对被连接表A2首先做一次筛选，让符合这一条件的记录参与与主表A1的连接，不符合这一条件的记录被剔除掉不予考虑，也没有机会参与连接和进入结果表。

（5）更改table_b的主键，将其设为（user_id,month_id），并添加一条记录如下：

这样再table_b中对应user_id=989832就有两条记录。

执行下面的SQL：

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
;

 

这样可以看到，最后查询结果集的记录数不再是14条记录，而是15条，这说明了一点，最后的结果集中的记录数并不是和主表中记录数一致的，而是由主表和被连接表根据连接条件共同确定的，还可以说明的一点是，不论on条件是什么样，多简单多复杂，只要没有where条件，最后的查询结果集中的记录数不会少于主表记录数。对于这个例子，主表中的14条记录都会进入结果表，由于主表中的连接条件只有User_Id相等，因此，对于A2表中的User_Id=989832由于存在两条记录，因此都会与A1表User_Id=989832的那一条记录相连，因此产生了两条连接结果记录，所以使得最后的结果集增至15条记录。

SELECT A1.User_Id
,A1.Month_Id
,A1.Brand_Id
,A1.Rate
,A2.User_Id
,A2.Month_Id
,A2.Type_Id
FROM table_a A1
LEFT JOIN table_b A2
ON A1.User_Id=A2.User_Id
AND A1.Month_Id=A2.Month_Id
;

 上面给A1和A2表的连接条件增加了一个Month_Id相等的条件，这样只有满足这一条件的A2表记录才会参与连接，这样就对A2表中的User_Id=989832剔除了一条记录，所以最后连接只产生了14条记录。

 

 

2、现有table_c，是用户访问上网品牌的频率表。Brand_Id是上网品牌，Rank_Id是它对于该用户的一个访问频率排名（Rate排名）。

现在要提取每个用户访问频率最高的前三项上网品牌，形成宽表，一个用户一条记录，没有的取空值。

SQL1： 

SELECT a1.User_Id
,a1.User_Id
,a1.Brand_Id AS brand_rank1
,a2.Brand_Id AS brand_rank2
,a3.Brand_Id AS brand_rank3
FROM table_c  AS a1
LEFT JOIN table_c AS a2
ON a1.Month_Id=a2.Month_Id
AND a1.User_Id=a2.User_Id
AND a2.Rank_Id=2
LEFT JOIN table_c AS a3
ON a1.Month_Id=a3.Month_Id
AND a1.User_Id=a3.User_Id
AND a3.Rank_Id=3
WHERE a1.Rank_Id=1
;

查询结果：

这正是我们期望的结果。

SQL2：

SELECT a1.User_Id
,a1.User_Id
,a1.Brand_Id AS brand_rank1
,a2.Brand_Id AS brand_rank2
,a3.Brand_Id AS brand_rank3
FROM table_c  AS a1
LEFT JOIN table_c AS a2
ON a1.Month_Id=a2.Month_Id
AND a1.User_Id=a2.User_Id
LEFT JOIN table_c AS a3
ON a1.Month_Id=a3.Month_Id
AND a1.User_Id=a3.User_Id
WHERE a1.Rank_Id=1
AND a2.Rank_Id=2
AND a3.Rank_Id=3
;

 查询结果：

 

3、综上所述，予以总结如下：

（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搭配完成。