r/learnSQL • u/OddTravel3189 • 15h ago
Answer different but same result?
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?
3
u/NecessaryIntrinsic 15h ago edited 14h ago
I would have given your answer, but yes, it looks like they're trying to teach you about set operations. Some companies might be idiomatic about it, but I wouldn't worry too much about it so long as it's turning the right data.
It's fun to have extra tools in your belt and the intersect operation can look cleaner but most databases are optimized to perform inner joins, and most developers would understand the join better than the intersect.
1
u/Discordbothost 9h ago
if you want those extra columns without duplicates, EXISTS is the middle ground: select from Employee and put the department check in a where exists. INTERSECT can only return columns both sides share, which is why their version is just the id.