-1

For two tables 'Employees' and 'Sales', if we want to get total sales done by employee with a specific employee name.

Employees table

EmployeeID,
EmployeeFirstName,
EmployeeLastName,
EmployeeEmailID,
EmployeeContactNo

Sales table

SalesID,
SalesEmployeeID,
SalesDate,
SalesTotal

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?

  • 2
    There was Reads for the 1st and 16 Reads for the other two. – I think there's a number (or, perhaps, a "no") missing immediately before the first "Reads". – Andriy M Jan 19 '18 at 9:23
  • 2
    As noted in one of the answers, the first query should error out if there are more than 1 rows with the name given. Should it perhaps be ... WHERE SalesEmployeeID IN (SELECT ..., rather than =? Or is EmployeeFirstName a unique field in your database? (I've worked in a company with less that 20 employees, and three Davids). – RDFozz Jan 19 '18 at 15:50
  • 1
    Where does this statement come from: However, common wisdom indicates that we should avoid subqueries in these circumstances, and use joins.? I don't really see why this should be (or is) common wisdom. – hot2use Jan 22 '18 at 10:07
  • 1
    The second and third are absolutely identical. There is no logical difference between the old, ancient, outdated implicit joins in the WHERE clause and the modern explicit JOIN operator. The sub-query however is counting something different – a_horse_with_no_name Jan 22 '18 at 14:15
1

As @a_horse_with_no_name just commented, the first query is different from number two and three (unless EmployeeFirstName is unique). The first query is invalid if more than one Employee has name GIVEN. For it to be valid:

(SELECT EmployeeID FROM Employees WHERE EmployeeFirstName = 'GIVEN')

must evaluate to a scalar value.

Query 2 and query 3 are semantically equivalent (and probably performs equally), but it is considered a good practice to use Q2.

A modern variant of Q3 is:

SELECT COUNT(SalesTotal), SUM(SalesTotal) 
FROM Sales s
CROSS JOIN Employees e 
WHERE s.SalesEmployeeID = e.EmployeeID 
  AND e.EmployeeFirstName = 'GIVEN';

but I would recommend Q2.

To conclude, I would prefer:

SELECT COUNT(SalesTotal), SUM(SalesTotal) 
FROM Sales s 
JOIN Employees e 
    ON s.SalesEmployeeID = e.EmployeeID 
WHERE e.EmployeeFirstName = 'GIVEN'

over the others.

  • I have enhanced and reworded my my question – user1451111 Jan 19 '18 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.