EXIST 子句是否只返回不同的行？

我有兩個看起來在邏輯上等效的查詢，但它們返回不同的結果集。我正在使用AdventureWorks2012數據庫。

第一個查詢使用帶有子查詢的 EXISTS 子句：

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
  ON e.BusinessEntityID = p.BusinessEntityID 
WHERE EXISTS
(SELECT *
   FROM HumanResources.Department AS d
   JOIN HumanResources.EmployeeDepartmentHistory AS edh
      ON d.DepartmentID = edh.DepartmentID
   WHERE e.BusinessEntityID = edh.BusinessEntityID
   AND d.Name LIKE 'P%')

第二個查詢僅使用 JOIN：

SELECT p.FirstName, p.LastName, e.JobTitle
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

第一個查詢返回 198 行，而第二個查詢返回 199。

然後，我使用 EXCEPT 子句對彼此執行兩個查詢，以查看哪一行正在消失：

SELECT  p.FirstName, p.LastName, e.JobTitle
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

EXCEPT

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
  ON e.BusinessEntityID = p.BusinessEntityID 
WHERE EXISTS
(SELECT *
   FROM HumanResources.Department AS d
   JOIN HumanResources.EmployeeDepartmentHistory AS edh
      ON d.DepartmentID = edh.DepartmentID
   WHERE e.BusinessEntityID = edh.BusinessEntityID
   AND d.Name LIKE 'P%')

令人驚訝的是，沒有返回任何行。但後來我使用 JOIN 在我的查詢中添加了一個 DISTINCT 子句，然後兩個查詢最終都返回了相同的結果集。

我是否正確假設要具有這種性質的邏輯等效查詢，我必須在僅使用 JOIN 的查詢中添加一個不同的子句？

使用這樣的子查詢有什麼好處？對我來說，JOIN 更有意義，並且更容易理解正在發生的事情。

EXISTS實際上不返回任何行。他們檢查存在，然後繼續前進。

您的問題可能是由連接到後兩個表的重複引起的。嘗試執行這個：

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) > 1

我猜你會回到一個計數為 2 的行。

您在 中看不到任何內容的原因EXCEPT是因為所有行都存在於兩個地方。您在第二個查詢中只有一個重複項。

重複的原因是因為使用 和HumanResources.employeeDepartmentHistory連接HumanResources.Department。基本上，該HumanResources.employeeDepartmentHistory表用於將一名員工映射到兩個部門。

JOIN這是將vsEXISTS用於您嘗試使用的邏輯類型的主要區別之一。

引用自：https://dba.stackexchange.com/questions/138286