ALL (TransactSQL)
Compares a scalar value with a singlecolumn set of values.
TransactSQL Syntax Conventions
Syntax
scalar_expression { =  <>  !=  >  >=  !>  <  <=  !< } ALL ( subquery )
Arguments
 scalar_expression
Is any valid expression.
 { =  <>  !=  >  >=  !>  <  <=  !< }
Is a comparison operator.
subquery
Is a subquery that returns a result set of one column. The data type of the returned column must be the same data type as the data type of scalar_expression.Is a restricted SELECT statement, in which the ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed.
Remarks
ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) would not meet the criteria of the expression.
For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME  ANY (TransactSQL).
This topic refers to ALL when it is used with a subquery. ALL can also be used with UNION and SELECT.
Result Types
Boolean
Result Value
Returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression**,**x), when x is a value in the singlecolumn set; otherwise returns FALSE.
Examples
The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID
in the AdventureWorks
database can be manufactured in the specified number of days. The example uses a subquery to create a list of the number of DaysToManufacture
value for all of the components of the specific SalesOrderID
, and then confirms that all the DaysToManufacture
are within the number of days specified.
USE AdventureWorks ;
GO
CREATE PROCEDURE DaysToBuild @OrderID int, @NumberOfDays int
AS
IF
@NumberOfDays >= ALL
(
SELECT DaysToManufacture
FROM Sales.SalesOrderDetail
JOIN Production.Product
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE SalesOrderID = @OrderID
)
PRINT 'All items for this order can be manufactured in specified number of days or less.'
ELSE
PRINT 'Some items for this order cannot be manufactured in specified number of days or less.' ;
To test the procedure, execute the procedure by using the SalesOrderID 49080
, which has one component requiring 2
days and two components that require 0 days. The first statement below meets the criteria. The second query does not.
EXECUTE DaysToBuild 49080, 2 ;
Here is the result set.
All items for this order can be manufactured in specified number of days or less.
EXECUTE DaysToBuild 49080, 1 ;
Here is the result set.
Some items for this order cannot be manufactured in specified number of days or less.
See Also
Reference
CASE (TransactSQL)
Expressions (TransactSQL)
Functions (TransactSQL)
LIKE (TransactSQL)
Operators (TransactSQL)
SELECT (TransactSQL)
WHERE (TransactSQL)
IN (TransactSQL)
Other Resources
Comparison Operators Modified by ANY, SOME, or ALL
Logical Operators
Subquery Fundamentals
Subquery Fundamentals
Help and Information
Getting SQL Server 2005 Assistance
Change History
Release  History 

17 July 2006 
