DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the ALL operator?

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

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE