CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Alex Gordon
Alex Gordon

Reputation: 60841

Understanding the behavior of a self-join on a condition

I've inherited the following code (only the first block), but do not understand the differences between

UPDATE Contact2 
SET Contact2.gbs_contactid = Contact1.gbs_contactId 
FROM gbs_sourcecontact Contact2 
INNER JOIN gbs_sourcecontact Contact1 
ON Contact1.gbs_sourcecontactid = '90BFC45B-8C75-4054-89D0-5400B225108C' 
WHERE Contact2.gbs_sourcecontactid =' E83C4FF3-4298-4CF3-8718-6CF987CE5803'

and

UPDATE Contact2 
SET Contact2.gbs_contactid = Contact1.gbs_contactId 
FROM gbs_sourcecontact Contact2 
INNER JOIN gbs_sourcecontact Contact1 
ON Contact1.gbs_sourcecontactid = '90BFC45B-8C75-4054-89D0-5400B225108C' 
AND Contact2.gbs_sourcecontactid =' E83C4FF3-4298-4CF3-8718-6CF987CE5803'

and

UPDATE Contact2 
SET Contact2.gbs_contactid = Contact1.gbs_contactId 
FROM gbs_sourcecontact Contact2 
INNER JOIN gbs_sourcecontact Contact1 
ON Contact2.gbs_sourcecontactid =' E83C4FF3-4298-4CF3-8718-6CF987CE5803'
WHERE Contact1.gbs_sourcecontactid = '90BFC45B-8C75-4054-89D0-5400B225108C'

I'm understanding the concept of inner joins; however, in this specific case I do not understand the behavior of what happens when switching the ON/WHERE clauses.

How will switching the ON/WHERE clauses affect the results?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

The switching of the conditions between the on and where clauses doesn't affect results for an inner join (see caveat below). These "join"s are confusing, because there is no real link between the tables. You are just choosing (presumably) single rows from the table and matching them up.

I would be inclined to write the join in the second fashion, with both conditions in the on clause. Mixing conditions in the on and where clauses leads to confusion -- and questions such as yours.

Caveat: If there are multiple matches in Contact1, then one of the values is chosen for the update (a value from an arbitrary matching row). The placement of the conditions in the on clause versus where clause probably doesn't affect the execution plan. But, you never know. There could be small differences that would result in different updates in this case.

Upvotes: 2

Related Questions