行为不当(Misbehaving)的子查询

     子查询有一个不知道是不是bug的设计问题.曾经出过一个错误,幸好在投入使用前发现问题,避免错误.
    要在数据库中返回数据:没有销售过商品给会员'EDWIN'的销售员工.SQL很简单,马上写道:

1. SELECT StaffID,StaffName
2. FROM dbo.MsStaff
3. WHERE StaffID NOT IN
4.   (SELECT StaffID FROM dbo.TxSales
5.   WHERE VipCode='EDWIN')

    奇怪的是,它返回一个空集,从业务上看,很明显是错误的,因为不可能全国所有员工都卖过商品给该会员.检查发现是因为TxSales表中的销售员工字段不叫StaffID,而是SalesMan.非常奇怪为什么SQL不报"无效列名"错误,而当我单独运行子查询部分时,是正常报错的.
    翻阅资料,得知:在外部查询的上下文中,该子查询是有效的!名称解释进行从内部嵌套级别向外进行.查询处理器先在当前级别引用的TxSales表中寻找StaffID列,未找到这个列名称后,它将在MsStaff表(外部级别)中查找并找到该列.子查询变成了相关子查询:

1. SELECT StaffID,StaffName
2. FROM dbo.MsStaff As M
3. WHERE StaffID NOT IN
4.   (SELECT M.StaffID FROM dbo.TxSales As S
5.   WHERE S.VipCode='EDWIN')

 逻辑上,该查询没有意义,但它在技术上是可行的.这样的SQL结果只有两种情况:
1)销售单中没有EDWIN这个会员,那么查询返回所有的销售员工
2)如上例,返回空集.

 

记录一下,以后写SQL要看清楚结果是否符合现实情况.数据是否在合理范围,因为通常错误的SQL返回的结果和业务数据差距会比较大,通过简单对比能够检查出部分错误.