For two tables 'Employees' and 'Sales', if we want to get total sales done by employee with a specific employee name.
The requirement can be fulfilled by the below 2 SQL statement. What is the performance difference between these three.
SELECT COUNT(SalesTotal), SUM(SalesTotal) FROM Sales WHERE SalesEmployeeID = (SELECT EmployeeID FROM Employees WHERE EmployeeFirstName = 'GIVEN') SELECT COUNT(SalesTotal), SUM(SalesTotal) FROM Sales s INNER JOIN Employees e ON s.SalesEmployeeID = e.EmployeeID WHERE e.EmployeeFirstName = 'GIVEN' SELECT COUNT(SalesTotal), SUM(SalesTotal) FROM Sales s, Employees e WHERE s.SalesEmployeeID = e.EmployeeID AND e.EmployeeFirstName = 'GIVEN'
I tried calculating the processing time using SQL Profiler and it was 0 ms for the 1st, 3 ms for the 2nd and 2 ms for the 3rd.
There was Reads for the 1st and 16 Reads for the other two.
UPDATE: I tested again after inserting multiple rows in Sales table against 'GIVEN' name and all the three queries are running in almost the same time, 1ms.
So - performance of the subquery version is no worse (and, appears sometimes better!) than either of the join versions. However, common wisdom indicates that we should avoid subqueries in these circumstances, and use joins. Why?