DATABASE & SQL/PLSQL

adplus-dvertising
Queries with modified Comparison Operators
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.

OperatorDescription

>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