Considerations for connecting SQL statements to on conditions

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
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 + ')

For example, if the above Code does not have a value when connecting left and right, it is replaced by null, which invalidates the conditions unrelated to the connection under on.

For example, 1. And vs. servicecustomertypecode ='s''

2. Av. auctionid = (select max (auctionvehicle. auctionid) from auctionvehicle where auctionvehicle. vehiclecontractid in ('+ @ vehiclecode + '))

3. And vs. servicesettleprice <> 0

These three conditions are not related to the connection. They are only filter conditions. Therefore, you must add them to the WHERE clause instead of put them in the on clause.

 

When innner joins, some filtering conditions in on are not ignored because they are strictly connected.

 

From: http://beijiguang-fred.blog.sohu.com/153417642.html