CODEWITHSUNDEEP
sql
gwin003
gwin003

Reputation: 7921

What is faster, JOIN AND or WHERE?

Which of these two is faster? Is there a difference or are they interchangeable?

Join using a WHERE clause:

delete PersonInfo from PersonInfo pi 
  join Person p on pi.PersonId = p.Id where p.Name = 'Smith'

Join using AND:

delete PersonInfo from PersonInfo pi 
  join Person p on pi.PersonId = p.Id and p.Name = 'Smith'

Using SQL Server 2012.

Upvotes: 1

Views: 724

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

Since the join is inner by default, there is no logical difference between these queries.

Any query optimizer worth its salt would produce identical execution plans for these two queries. Here is the execution plan that I see for both cases:

Execution Plan

(the plan that you see will be different, because the sample tables I created are not indexed, while your tables would very likely have suitable indexes).

Had the join been outer, there would be a difference between the two queries: the second one would delete all rows of PersonInfo that have no corresponding rows in the Person table, in addition to deleting the info for all 'Smith's.

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

I think an even faster way would be ......

DELETE FROM PersonInfo
WHERE EXISTS (SELECT 1 
              FROM Person 
              WHERE PersonInfo.PersonId = Person.Id 
              AND Person.Name = 'Smith')

Upvotes: 1

Related Questions