Does the EXIST clause only return distinct rows by its nature?
EXISTS don't actually return any rows.  They check for the existence and then move on.

Your problem is probably a duplicate caused by the JOINs to the second two tables.  Try running this:

SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID 
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID
GROUP BY p.FirstName, p.LastName, e.JobTitle
HAVING COUNT(1) &gt; 1


I'm guessing you will get back one row with a count of 2.

The reason you aren't seeing anything in the EXCEPT is because all of the rows do exist in both places.  You just have a duplicate in the second query.

The reason for the duplicate is because either the joins with HumanResources.employeeDepartmentHistory and HumanResources.Department.  Basically the HumanResources.employeeDepartmentHistory table is used to map one employee to two departments.

This is one of the major differences in using a JOIN vs EXISTS for the type of logic you are trying to use.