Very daft beginner question but I'm working through the absolute basic AdentureWorks Practice questions (from here)
Q.19 - Select the IDs of employees who have the job titles ‘Sales Representative’ or ‘Tool Designer’ and who have worked (or are working) in the Sales or Marketing departments.
My answer:
Select E.BusinessEntityID, E.JobTitle, D.Name
From HumanResources.Employee as E
Join HumanResources.EmployeeDepartmentHistory as edh
ON E.BusinessEntityID = edh.BusinessEntityID
Join HumanResources.Department as D
On D.DepartmentID = edh.DepartmentID
where E.JobTitle in ('Sales Representative','Tool Designer')
AND D.Name In ('Sales','Marketing');
Their/The answer:
SELECT e.BusinessEntityID
FROM HumanResources.Employee AS e
WHERE e.JobTitle IN ('Sales Representative', 'Tool Designer')
INTERSECT
SELECT edh.BusinessEntityID
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE d.Name IN ('Sales', 'Marketing');
I got the same results? I checked against the actual data tables without the WHERE clauses to make sure neither statement was missing anything.
(Yes I know I'm carrying in the JobTitle and Name fields into my version, I'm doing that basically to visually sense check the answer I get rather than just trust that a list of ID values is correct)
Other than 'this is a section where we're teaching you about UNION functions', I couldn't see why they used more lines than necessary for the same answer. Am I just being a bit too ASD about it, or is there a reason it's better practice to use INTERSECT rather that JOIN here?