sql server - How below two update statements are different in SQL? -


this question has answer here:

i have 2 update statements. both of them gives same output. how different , better use?

statement 1:

update li set li.description = im.description tbsupplierlineitem li join tbimpacarchievenew im on li.itemid = im.commomcode  li.processeddate >= convert(date,getdate()) 

statement 2:

update li set li.description = im.description tbsupplierlineitem li join tbimpacarchievenew im on li.itemid = im.commomcode  , li.processeddate >= convert(date,getdate()) 

with inner join, difference semantic difference. both queries should produce exact same query plan , exact same result.

however, when using outer joins, matters if condition on where clause or on on clause.

update li set li.description = im.description tbsupplierlineitem li right join tbimpacarchievenew im on li.itemid = im.commomcode                                  , li.processeddate >= convert(date,getdate()) 

is different

update li set li.description = im.description tbsupplierlineitem li right join tbimpacarchievenew im on li.itemid = im.commomcode  li.processeddate >= convert(date,getdate()) 

not on semantic level.
while first query return expected result of right join, second 1 infact return results expected inner join.
because right table values might null if have records on left table doesn't match them, , since comparing value null (including null) result false, it's changing right join inner join.


Comments

Popular posts from this blog

python - Selenium remoteWebDriver (& SauceLabs) Firefox moseMoveTo action exception -

html - How to custom Bootstrap grid height? -

transpose - Maple isnt executing function but prints function term -