业务背景:
一个存放政策的表a,一个存放政策类型的表b。b表的主键lx_id,是a表的外键。
zc_id | 政策id |
zcmc | 政策名称 |
sfsc | 是否删除 |
lx_id | 类型id |
lx_id | 类型id |
lxmc | 类型名称 |
sfsc | 是否删除 |
业务要求:
在原来只查询政策的基础上查出政策类型名称。
过程:
#原来单表查询(举例说明):
select zc.zc_id,zc.zcmc,zc.sfsc from rczc zc
被我不假思索的改成:
select zc.zc_id,zc.zcmc,lx.lxmc from gyrlzyw_rcfw_rczc zc,gyrlzyw_rcfw_rczclx lx where zc.lx_id = lx.lx_id;
咦,怎么少了一条了数据了? 原来有一个政策是没有类型的,所以用外连接来解决这个问题。
(left join on :以左边的表为主表,把右边表的数据附加到左表上面;right join on反之 )
select zc.zc_id,zc.zcmc,zc.sfsc,lx.lxmc from rczc zc LEFT JOIN rczclx lx on zc.lx_id = lx.lx_id
现在又要求只查出来没有被删除的数据、直接码代码如下:
select zc.zc_id,zc.zcmc,zc.sfsc,lx.lxmc from rczc zc LEFT JOIN rczclx lx on zc.lx_id = lx.lx_id and zc.sfsc=0
咦咦咦,没变化呀!,回头看看上面的代码,and zc.sfsc=0 直接写在了on zc.lx_id = lx.lx_id 后面,这样写是无效的呢,为什么呢?
其实当连接多张表查询时,会生成一张临时表,然后再返回临时表;on后面的条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。where条件是在临时表生成后,再对临时表进行过滤的条件。修改SQL为:
select zc.zc_id,zc.zcmc,zc.sfsc,lx.lxmc from rczc zc LEFT JOIN rczclx lx on zc.lx_id = lx.lx_id where zc.sfsc=0
那on 后面的条件既然不影响返回数据的条数,那会影响什么呢?看个例子,注意on 后面的条件被我改成了on zc.zc_id = lx.lx_id,很明显前面的政策id和后面的类型id是不肯能对上的,看看结果:
select zc.zc_id,zc.zcmc,zc.sfsc,lx.lxmc from rczc zc LEFT JOIN rczclx lx on zc.zc_id = lx.lx_id
结果条数没变,类型名称都没了,所以on后面的条件只会影响附加到主表上的数据,而不会影响主表的数据。