In SQL programming, while using subqueries, you can use the =,>, and < comparison operators to create a condition that checks the value returned by the subquery. When a subquery returns more than one value, you might need to apply the operators to all the values returned by the subquery. To perform this task, you can modify the comparison operators in the subquery. The SQL Server provides the ALL and ANY keywords that can be used to modify the existing comparison operators.
The ALL keyword returns a TRUE value, if all the values that are retrieved by the subquery satisfy the comparison operator. It returns a FALSE value if only some values satisfy the comparison operator or if the subquery does not return any rows to the outer statement.
The ANY keyword returns a TRUE value if any value that is retrieved by the subquery satisfies the comparison operator. It returns a FALSE value if no values in the subquery satisfy the comparison operator or if the subquery does not return any rows to the outer statement.
The following list shows the operators that can be used with the ALL and ANY keywords:
The following example displays the employee ID column and the title of all the employees whose vacation hours are more than the vacation hours of employees designated as Recruiter:
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE VacationHours >ALL (SELECT VacationHours
FROM HumanResources.Employee WHERE JobTitle = 'Recruiter')
In the preceding example, the inner query returns the vacation hours of all the employees who are titled as recruiter. The outer query uses the ‘>ALL’ comparison operator. This retrieves the details of those employees who have vacation hours greater than all the employees titled as recruiter.
The output of the query is displayed in the following figure.
The ALL keyword returns a TRUE value, if all the values that are retrieved by the subquery satisfy the comparison operator. It returns a FALSE value if only some values satisfy the comparison operator or if the subquery does not return any rows to the outer statement.
The ANY keyword returns a TRUE value if any value that is retrieved by the subquery satisfies the comparison operator. It returns a FALSE value if no values in the subquery satisfy the comparison operator or if the subquery does not return any rows to the outer statement.
The following list shows the operators that can be used with the ALL and ANY keywords:
- >ALL, greater than the maximum value in the list.
The expression|column_name> ALL (10, 20, 30) means ‘greater than 30’ - >ANY, greater than the minimum value in the list.
The expression|column_name >ANY (10, 20, 30) means ‘greater than 10’ - =ANY, any of the values in the list. It acts in the same way as the IN clause.
The expression|column_name = ANY (10, 20, 30) means ‘equal to either 10 or 20 or 30’ - <>ANY, not equal to any value in the list.
The expression|column_name <>ANY (10, 20, 30) means ‘not equal to 10 or 20 or 30’
- <>ALL, not equal to all the values in the list. It cats in the same way as the NOT IN clause.
The expression|column_name <>ALL (10, 20, 30) means ‘not equal to 10 and 20 and 30’
The following example displays the employee ID column and the title of all the employees whose vacation hours are more than the vacation hours of employees designated as Recruiter:
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE VacationHours >ALL (SELECT VacationHours
FROM HumanResources.Employee WHERE JobTitle = 'Recruiter')
In the preceding example, the inner query returns the vacation hours of all the employees who are titled as recruiter. The outer query uses the ‘>ALL’ comparison operator. This retrieves the details of those employees who have vacation hours greater than all the employees titled as recruiter.
The output of the query is displayed in the following figure.
