In ,Exists,Some,Any

最新推荐文章于 2025-06-30 15:45:02 发布

原创最新推荐文章于 2025-06-30 15:45:02 发布 · 411 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null

SQL Server 专栏收录该内容

8 篇文章

订阅专栏

本文探讨了SQL中IN与EXISTS的区别，特别是在处理NULL值时的行为差异，并通过示例展示了如何使用NOT IN、NOT EXISTS以及比较运算符进行高效的数据筛选。此外，还提供了几种不同的SQL查询方法来找出特定条件下的最小OrderID。

In 和 Exists区别：

val in (val1,val2,...Null) 永远不会返回false，而是返回TRUE和Unknown

当存在NULL时, not in 和 not exists不等价！

v<>any(x,y,z) => v<>x or v <>y or v <>z

v<>all(x,y,z) => v<>x and v <.y and v<>z

具有最小OrderID的值

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS O1
WHERE NOT OrderID >
  ANY(SELECT OrderID
      FROM dbo.Orders AS O2
      WHERE O2.EmployeeID = O1.EmployeeID);

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS O1
WHERE OrderID <=
  ALL(SELECT OrderID
      FROM dbo.Orders AS O2
      WHERE O2.EmployeeID = O1.EmployeeID);

-- The Natural Way
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS O1
WHERE OrderID =
  (SELECT MIN(OrderID)
   FROM dbo.Orders AS O2
   WHERE O2.EmployeeID = O1.EmployeeID);
GO