In ,Exists,Some,Any

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