In SQL Server the logical operators are used to perform conditional checks in SQL Queries. There are many different types of Logical operators like ANY, AND, LIKE, EXISTS, NOT, IN, and BETWEEN in SQL Server. These operators are very useful to filter and compare single or multiple data in SQL Queries. In this article let us discuss the ‘ALL’ operator available in SQL Server.
ALL Operator
The ALL operator compares a scalar expression with the values from a subquery result set. It returns a Boolean TRUE or FALSE as a result. It returns TRUE if the subquery result set values meet the condition. The data type of the single column used in the subquery ‘Select’ statement should be the same as the scalar expression.
Syntax:
scalar_expression { comparison_operator} ALL ( subquery )
- scalar_expression: This can be any valid expression.
- comparison_operator: From one of the comparison operators list – = , <> , != , > , >= , !> , < , <= , !<
- subquery: This is a ‘SELECT’ statement with a single column that returns a result set.
How Do the ‘ALL’ Operators Work?
- The ALL operator verifies the result set returned by the subquery satisfies the condition
- The ALL operator can be used when you want to check if at least one value satisfies the condition
- The Subquery after the ALL operator must return a single column and the column should be of same data type as the scalar expression column type.
Examples of How to Use ALL Operator
Below are examples to explain how the ALL operator works
Sample Data tables used in the examples
Products Table:
Products Table
Orders Table:
Orders Table
Example 1
The below example query explains of displaying products which are other than specific category:
Select * from Products where ProductID <> ALL (Select ProductID from Products where categoryId = 2)
Output:
Example 1 Output
Example 2
The below example query explains how to check for all Products Sold NOT within the price range of 20 and 40:
Select * from Products where Price !=ALL (Select Price from Products where Price Between 20 and 40)
Output:
Example 2 Output
Example 3
The below example query explains about, how to check for Specific Products Ordered based on OrderID:
Select * from Products where ProductID > ALL (Select ProductID from OrderDetails where OrderID = 10250 )
Output:
Example 3 Output
Conclusion
To summarise, the ‘ALL’ operator in SQL Server can be used to compare a value from scalar expression to all values returned by a subquery using a comparison operator. It efficiently verifies whether the condition holds true for the entire subquery result set. The ‘ALL’ operator is particularly useful when ensuring that a specified condition is met across all values, providing flexibility in querying and filtering data in SQL Server.