SQL Standard

SQL Any Operator

The ANY operator in SQL allows us to compare a value within a set of values. The Any operator uses comparison operators to compare a value from a group of values in a subquery.

Operator Syntax

The syntax for the ANY operator in SQL is as shown:

WHERE column_name comparison_operator ANY (subquery)

The ANY operator returns the column if the specified condition is true.

Practical Example

Suppose we have a table containing product information as shown:

We can use the ANY operator to find all the products whose quantity is greater than the average of the total quantities.

An example query is as shown:

SELECT * FROM products
WHERE quantity > ANY (
    SELECT avg(quantity)
    FROM products
);

This should return all the products whose quantity is greater than the average, as shown:

To find the products whose quantity is less than average, change the comparison operator as shown:

SELECT * FROM products
WHERE quantity < ANY (
    SELECT avg(quantity)
    FROM products
);

This should return:

You can use other operators as shown in the queries below:

-- greater than or equal to

SELECT * FROM products
WHERE quantity >= ANY (
    SELECT avg(quantity)
    FROM products
);
-- less than or equal to
SELECT * FROM products
WHERE quantity <= ANY (
    SELECT avg(quantity)
    FROM products
);
-- not equal to
SELECT * FROM products
WHERE quantity != ANY (
    SELECT avg(quantity)
    FROM products
);
-- equal to
SELECT * FROM products
WHERE quantity = ANY (
    SELECT avg(quantity)
    FROM products
);

Feel free to experiment with various scenarios and operators to understand better how to use any operator.

Conclusion

This tutorial shows you how to use the ANY operator to compare a value against a set of values.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list