1

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.

gwin003
  • 7,432
  • 5
  • 38
  • 59
  • 1
    In any reasonable database, they would have the same execution plan. – Gordon Linoff Aug 07 '14 at 17:56
  • @GordonLinoff Using SQL Server 2012, so should be the same. I was just curious. Thanks. – gwin003 Aug 07 '14 at 17:56
  • Check these - http://stackoverflow.com/questions/121631/inner-join-vs-where http://stackoverflow.com/questions/10848975/performance-difference-condition-placed-at-inner-join-vs-where-clause – ND27 Aug 07 '14 at 18:06

2 Answers2

4

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.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
1

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')
M.Ali
  • 67,945
  • 13
  • 101
  • 127