2

What is difference between below queries

Select  E.EmployeeCode,E.Name,D.DepartmentCode,D.DepartmentName
From Employee E
inner join Department D on D.DepartmentID=E.DepartmentID
Where e.EmployeeType='Developer'

Vs

Select  E.EmployeeCode,E.Name,D.DepartmentCode,D.DepartmentName
From Employee E
inner join Department D on D.DepartmentID=E.DepartmentID And e.EmployeeType='Developer'

I am asking in terms of performance. How above query performe with huge data in tables

Ullas
  • 11,450
  • 4
  • 33
  • 50
user484948
  • 69
  • 1
  • 9
  • 3
    No difference at all, when you're doing an inner join. (When it comes to outer join, they've got different results...) – jarlh Mar 07 '16 at 10:38
  • 5
    It's popular problem and there was several threads like this. For example: http://stackoverflow.com/questions/1018952/condition-within-join-or-where – Matt Mar 07 '16 at 10:39
  • For outer joins there may be significant difference. For the cases where condition placing does not affect result this is only a style and personal preferences question. Not related to the join itself conditions I'd recommend to place in `where`. Like your `employeetype='developer'`. It is not related to joining and is a bit confusing. Like you forgot something or actually wanted a `left join`. – Ivan Starostin Mar 07 '16 at 11:14

1 Answers1

3

For an INNER JOIN there is no difference. The execution plan for each query is identical.

SQL Execution Plan

The first plan is for the query:

Select  E.EmployeeCode,E.Name,D.DepartmentCode,D.DepartmentName
From Employee E
inner join Department D on D.DepartmentID=E.DepartmentID
Where e.EmployeeType='Developer'

And the second plan is for the query:

Select  E.EmployeeCode,E.Name,D.DepartmentCode,D.DepartmentName
From Employee E
inner join Department D on D.DepartmentID=E.DepartmentID And e.EmployeeType='Developer'

Therefore the performance of either query will be the same.

With "huge" data you may need indexes on the columns you are using in the WHERE and JOIN condition, depending on the uniqueness of values in those columns. In the query plans in the screenshot you can see SQL Sever is performing a Table Scan, so you might want to change that.

Ɖiamond ǤeezeƦ
  • 3,223
  • 3
  • 28
  • 40