[转]sql 左右连接 on 条件注意事项

最新推荐文章于 2022-10-10 11:35:40 发布

雨枫

最新推荐文章于 2022-10-10 11:35:40 发布

阅读量590

点赞数

文章标签： sql join null

select *   FROM View_VehicleContractInfo vci WITH(NOLOCK)
   LEFT JOIN AuctionVehicle av WITH(NOLOCK)
    ON vci.VehicleContractID = av.VehicleContractID
    AND av.AuctionID = (SELECT MAX(AuctionVehicle.AuctionID) FROM AuctionVehicle WHERE AuctionVehicle.VehicleContractID IN ('+@VehicleCode+'))
   LEFT JOIN View_VehicleServiceInfo AS VS
    ON vci.VehicleCode = VS.VehicleCode
    AND VS.ServiceCustomerTypeCode=''S''
    AND VS.ServiceSettlePrice<>0 and VS.auctionid=av.auctionid
   LEFT JOIN StoreManagement AS SM ON vci.VehicleCode = SM.GPcode
   WHERE vci.VehicleContractID IN ('+@VehicleCode+')

例如上面的代码在左右连接的时候如果没有值则用null代替，这就使得on下和连接无关的条件失效

如 1. AND VS.ServiceCustomerTypeCode=''S''

2. av.AuctionID = (SELECT MAX(AuctionVehicle.AuctionID) FROM AuctionVehicle WHERE AuctionVehicle.VehicleContractID IN ('+@VehicleCode+'))

3. AND VS.ServiceSettlePrice<>0

这三条和连接无关，只是一些过滤的条件，所以一定要加到 where里而不是放到on里

innner join的时候因为是严格的连接，所以on里放一些过滤条件是不会被忽略的。

转自：http://beijiguang-fred.blog.sohu.com/153417642.html