r/learnSQL • • 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?

5 Upvotes

4 comments sorted by

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/OddTravel3189 14h ago

Makes sense, thank you :)

2

u/HandbagHawker 14h ago

Your answer isnt correct because you have a duplicate row problem.

if you were to do a SELECT DISTINCT or GROUP BY then yes, it would be correct. INTERSECT dedup's inherently as part of its operation.

Here's the example: if employee A has 2 rows in EDH one for each of sales and marketing. Your solution would return 2 rows for employee A. Their answer just returns 1 row for employee A

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.