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:
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:
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:
WHERE quantity < ANY (
SELECT avg(quantity)
FROM products
);
This should return:
You can use other operators as shown in the queries below:
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!!
