SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

ALL Logical Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 4120

This article explains use of ALL logical operator with different examples.

ALL logical operator compares a scalar value with a single-column set of values.

Syntax of Logical Operator ALL : 

scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )

scalar_expression is valid expression in sql server.

{ = | <> | != | > | >= | !> | < | <= | !< } is a comparison operator.

Subquery is a query 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. ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed in subquery).

Return type of ALL operator is boolean. It returns TRUE when the comparison specified is TRUE for all pairs(scalar_expression, x)  where x is a value in the single-column set otherwise it returns FALSE.

Example of  Logical Operator ALL :

Example 1 : Using all operator with subquery in a where clause 

SELECT  OrderID, Subtotal

FROM  Order_Subtotals

WHERE  Subtotal > ALL(SELECT SaleAmount

                                         FROM Sales_Totals_by_Amount)




OrderID         Subtotal

10865               16387.50

10981               15810.00

11030               12615.05

10889               11380.00

Above query instructs the outer query to select records whose Subtotal is greater than all the values of SaleAmount from Sales_Totals_by_Amount. It means it will reaturn records whose value is greater than the greatest value returned by the subquery. 

You can achieve same result using max aggregate function in the subquery. To view this example,please visit below link.

Max Function



Didn't find what you were looking for? Find more on ALL Logical Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of ALL Logical Operator is from United States.
View All Articles

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!