|
Execute the following
SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the use of ALL, SOME and ANY set comparison operators.
-- SQL ALL, ANY, SOME set operators - SQL inner join - SQL subquery, subselect
USE AdventureWorks;
SELECT ProductName = p.Name,
-- SQL money currency conversion
TotalOrder = '$' + convert(VARCHAR,TotalDue,1)
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON p.ProductID = sod.ProductID
WHERE soh.OrderDate BETWEEN '1/1/2003' AND '12/31/2003'
AND sod.OrderQty >
-- T-SQL ALL operator
ALL (SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderId = sod.SalesOrderID
WHERE soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')
GO
/* Results
ProductName TotalOrder
Full-Finger Gloves, L $104,111.35
Women's Mountain Shorts, S $144,026.40
Women's Mountain Shorts, L $149,286.68
*/
-- SQL ANY
SELECT ProductName = p.Name,
TotalOrder = '$' + convert(VARCHAR,TotalDue,1)
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON p.ProductID = sod.ProductID
WHERE soh.OrderDate BETWEEN '1/1/2003' AND '12/31/2003'
AND sod.OrderQty >
-- T-SQL ANY operator
ANY (SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderId = sod.SalesOrderID
WHERE soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')
GO
/* Partial results
ProductName TotalOrder
Mountain-300 Black, 38 $47,633.19
Mountain-300 Black, 40 $47,633.19
Mountain-300 Black, 48 $47,633.19
Mountain-200 Black, 38 $47,633.19
*/
-- SQL SOME
SELECT ProductName = p.Name,
TotalOrder = '$' + convert(VARCHAR,TotalDue,1)
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product AS p
ON p.ProductID = sod.ProductID
WHERE soh.OrderDate BETWEEN '1/1/2003' AND '12/31/2003'
AND sod.OrderQty >
-- T-SQL SOME operator
SOME (SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderId = sod.SalesOrderID
WHERE soh.OrderDate BETWEEN '1/1/2001' AND '12/31/2002')
GO
/* Partial results
ProductName TotalOrder
Mountain-300 Black, 38 $47,633.19
Mountain-300 Black, 40 $47,633.19
Mountain-300 Black, 48 $47,633.19
Mountain-200 Black, 38 $47,633.19
Mountain-200 Black, 42 $47,633.19
*/ |
