sql server - How below two update statements are different in SQL? -
this question has answer here:
- sql join: clause vs. on clause 13 answers
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
Post a Comment