How below two update statements are different in SQL?

Question

I have two update statements. Both of them gives the same output. How they are different and which is better to use?

Statement 1:

UPDATE li
SET li.Description = im.Description
FROM tbSupplierLineItem li
JOIN tbIMPACArchieveNew im ON li.ItemId = im.CommomCode 
WHERE li.ProcessedDate >= CONVERT(DATE,GETDATE())

Statement 2:

UPDATE li
SET li.Description = im.Description
FROM tbSupplierLineItem li
JOIN tbIMPACArchieveNew im ON li.ItemId = im.CommomCode 
AND li.ProcessedDate >= CONVERT(DATE,GETDATE())

There is no real difference. Some people prefer using `where`, because the condition is filtering on a single table. — Gordon Linoff, Jul 26 '17 at 11:09
No difference for an INNER JOIN (which this is). Big difference for an OUTER JOIN. — Bacon Bits, Jul 26 '17 at 11:10
It's like datasets that are being joined, if you put more criteria inside of JOIN clause it's like "only part of table that is fulfilling that criteria is being joined", if you have where, first it will join ALL data from tables that fulfilling that "ItemId" criteria and then filter it out to match where criteria — Veljko89, Jul 26 '17 at 11:13

Zohar Peled · Accepted Answer · 2017-07-26T11:32:22.990

With an inner join, the difference is only a semantic difference. Both queries should produce the exact same query plan and the exact same result.

However, when you are using outer joins, then it matters if the condition is on the where clause or on the on clause.

UPDATE li
SET li.Description = im.Description
FROM tbSupplierLineItem li
RIGHT JOIN tbIMPACArchieveNew im ON li.ItemId = im.CommomCode 
                                AND li.ProcessedDate >= CONVERT(DATE,GETDATE())

Is different than

UPDATE li
SET li.Description = im.Description
FROM tbSupplierLineItem li
RIGHT JOIN tbIMPACArchieveNew im ON li.ItemId = im.CommomCode 
WHERE li.ProcessedDate >= CONVERT(DATE,GETDATE())

not only on the semantic level.
While the first query will return the expected result of a right join, the second one will infact return the results expected from an inner join.
That is because the right table values might be null if you have records on the left table that doesn't match them, and since comparing any value to null (including another null) will result with a false, it's basically changing the right join to an inner join.

score 1 · Answer 2 · answered Jul 26 '17 at 11:13

Whether there is a difference between putting a filter predicate in the WHERE clause or an ON clause depends solely on the type of JOIN you're doing. For inner join, there is no difference. For outer joins, putting the predicate in the ON clause might mean you don't have to write OR xxx IS NULL in the WHERE clause also

Example. These two queries function identically:

SELECT *
FROM
  a
  LEFT OUTER JOIN
  b
  ON
    a.id = b.id AND
    b.type = 'circle'

SELECT *
FROM
  a
  LEFT OUTER JOIN
  b
  ON
    a.id = b.id
WHERE
    b.type = 'circle' OR b.id IS NULL

Omitting the OR b IS NULL from the second query could cause some rows to disappear from the results, if there is a row in A that does not have a corresponding row in B

How below two update statements are different in SQL?

2 Answers2

Linked

Related