The ANY operator:
- returns a boolean value as a result
- returns TRUE if any of the sub-query values meet the condition
Any means that the condition will be true if the operation is true for any of the values in the range.
DECLARE @Orders TABLE (
OrderID INT PRIMARY KEY,
CustomerID INT,
ProductID INT
);
INSERT INTO @Orders (OrderID, CustomerID, ProductID)
VALUES
(1, 101, 1),
(2, 101, 2),
(3, 102, 1),
(4, 102, 2),
(5, 102, 3),
(6, 103, 1),
(7, 103, 3);
SELECT * FROM @Orders
WHERE ProductID = ANY (SELECT ProductID from @Orders where CustomerID = 102);
--The first query selects orders with the same product that CustomerID 102 has ordered.
The ALL operator:
- returns a boolean value as a result
- returns TRUE if all of the sub-query values meet the condition
All means that the condition will be true if the operation is true for all of the values in the range.
SELECT * FROM @Orders
WHERE ProductID <> ALL (SELECT ProductID from @Orders where CustomerID = 103);
--The second query selects orders with products that no other customers have ordered.