Skip to content

Latest commit

 

History

History
118 lines (86 loc) · 5.11 KB

all-transact-sql.md

File metadata and controls

118 lines (86 loc) · 5.11 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom f1_keywords helpviewer_keywords dev_langs monikerRange
ALL (Transact-SQL)
ALL (Transact-SQL)
rwestMSFT
randolphwest
07/25/2022
sql
t-sql
reference
ignite-2024
ALL_TSQL
ALL
single-column set of values [SQL Server]
ALL (Transact-SQL)
TSQL
=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

ALL (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Fabricsqldb]

Compares a scalar value with a single-column set of values.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL 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 and the INTO keyword aren't allowed.

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 single-column set. Otherwise returns FALSE.

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) wouldn't 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 (Transact-SQL).

To compare the equality of two expressions and guarantee a true or false result, see IS [NOT] DISTINCT FROM (Transact-SQL).

This article refers to ALL when it is used with a subquery. ALL can also be used with UNION and SELECT.

Examples

The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the [!INCLUDEssSampleDBnormal] database can be manufactured in the specified number of days. The example uses a subquery to create a list of the number of DaysToManufacture values for all of the components of the specific SalesOrderID, and then confirms that all the DaysToManufacture are within the number of days specified.

-- Uses AdventureWorks

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 can''t 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 doesn't.

EXECUTE DaysToBuild 49080, 2 ;

[!INCLUDEssResult]

All items for this order can be manufactured in specified number of days or less.

EXECUTE DaysToBuild 49080, 1 ;

[!INCLUDEssResult]

Some items for this order can't be manufactured in specified number of days or less.

See also