| Previous | Home | Next |
SQL server provides the ALL and ANY keywords that can be used to modify the existing comparison operator. The sub query introduced with a modified comparison operator returns zero or more values and can be implemented using the GROUP BY or HAVING clause.
| Operator | Description |
>ALL |
Means greater than the maximum value in the list. column_name>ALL (10,20,30) means 'greater than 30' |
| >ANY | Means greater than the minimum value in the list. column_name>ANY (10,20,30) means 'greater than 10' |
| =ANY | Means any of the values in the list. It acts in the same way as the IN clause column_name=ANY (10,20,30) means 'equal to either 10 or 20 or 30' |
| <>ANY | Means not equal to any in the list. column_name<>ANY (10,20,30) means 'not equal to either 10 or 20 or 30' |
| <>ALL | Means not equal to all the values in the list. It acts in the same way as the NOT IN clause. column_name<>ALL (10,20,30) means 'not equal to either 10 and 20 and 30' |
Example
SELECT title_id ,title FROM titles WHERE price> ALL (SELECT price FROM titles WHERE pub_id='0736')
output
Display the title_id and title where price is greater than the maximum price of books published by the publisher with the publisher ID 0736
SELECT title_id ,title FROM titles WHERE price>ANY(SELECT price FROM titles WHERE pub_id='0736')
output
Display the title_id and title where price is greater than the minimum price of books published by the publisher with the publisher ID 0736
| Previous | Home | Next |