Is there any performance or functional difference between these two SQL statements?

Question

I'm maintaining someone else's SQL at the moment, and I came across this in a Stored Procedure:

    SELECT      
    Location.ID, 
    Location.Location, 
    COUNT(Person.ID) As CountAdultMales
FROM        
    Transactions INNER JOIN 
    Location ON Transactions.FKLocationID = Location.ID INNER JOIN  
    Person ON Transactions.FKPersonID = Person.ID 
     AND DATEDIFF(YEAR, Person.DateOfBirth, GETDATE()) >= 18 AND Person.Gender = 1
WHERE
    ((Transactions.Deleted = 0) AND
    (Person.Deleted = 0) AND
    (Location.Deleted = 0))

Is there any difference between the above and this (which is how I would write it)

SELECT      
    Location.ID, 
    Location.Location, 
    COUNT(Person.ID) As CountAdultMales
FROM        
    Transactions INNER JOIN 
    Location ON Transactions.FKLocationID = Location.ID INNER JOIN  
    Person ON Transactions.FKPersonID = Person.ID
WHERE
    ((Transactions.Deleted = 0) AND
    (Person.Deleted = 0) AND
    (Location.Deleted = 0) AND
    (DATEDIFF(YEAR, Person.DateOfBirth, GETDATE()) >= 18) AND
    (Person.Gender = 1))

Personally, I find putting the conditions in the WHERE clause most readable, but I wondered if there were performance or other reasons to "conditionalise" (if there is such a word) the JOIN

Thanks

On my SQL Server 2008R2 the execution plans generated are identical. — jpw, Jul 23 '13 at 10:45

score 3 · Answer 1 · answered Jul 23 '13 at 10:41

With an inner join this wont really make much of a difference as SQL has a query optimiser which will do its best to excecute the query in the most efficiant way (not perfect).

If this was an outer join it could make a difference though so its something to be aware of

score 3 · Answer 2 · answered Jul 23 '13 at 10:44

3

both query's performance is same. There is no difference for inner join.

answered Jul 23 '13 at 10:44

Hiren gardhariya

1,247
10
29

score 2 · Accepted Answer · answered Jul 23 '13 at 10:52

Performance is the same in your examples, however you can tune it this way:

SELECT      
Location.ID, 
Location.Location, 
COUNT(Person.ID) As CountAdultMales
FROM        
Transactions 
INNER JOIN Location 
ON Transactions.FKLocationID = Location.ID 
INNER JOIN Person 
ON Transactions.FKPersonID = Person.ID 
WHERE
Transactions.Deleted = 0 AND
Person.Deleted = 0 AND
Location.Deleted = 0 AND 
Person.DateOfBirth < dateadd(year, datediff(year, 0, getdate())-17, 0) AND 
Person.Gender = 1

This way you are not making a calculation on all columns to get the year. instead you will simply be comparing the year with a static value which is much faster.

This query is selecting rows where people turns 18(or are older) before current year runs out.

score 2 · Answer 4 · answered Jul 23 '13 at 10:55

Don't worry about the exec plan here, as SO members already stated, they should yield identical exec plan. That much about the question itself.

You should worry about code readability and maintaining if it is too early for it or there is little to none optimization possible.

Should that be really a join criteria or a filter? From just looking at the code itself I think it's part of the filter.

score 1 · Answer 5 · edited May 23 '17 at 10:25

1

It depends which DB you are working with. In general the DB internal optimizer will sort this out.

Please have a look here: INNER JOIN ON vs WHERE clause

edited May 23 '17 at 10:25

Community

1
1

answered Jul 23 '13 at 10:41

Guido

926
2
10
19

Is there any performance or functional difference between these two SQL statements?

5 Answers5