Sql ANY Operator

The SQL ANY operator are used on subqueries that return multiple values.

The SQL ANY operator returns true if any of the subquery values (single value or from list of values) meet the condition on outer table query.

It can be used on any valid sql select statement with where and having clause.

• The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Main Article :- Sql difference between ALL and ANY Operator

You can compare subqueries that provide several rows using the ANY and ALL operators in SQL. ANY and ALL check whether any or all of the values returned by a subquery fulfil the expression on the left. It works in the same way as the SOME and IN operators and must be followed by a comparison operator.

In SQL subquery comparisons, the ANY and ALL keywords are used to evaluate a set of values against all values in the output or any one value in the list.

The SQL ANY and ALL operators are logical operators, which means that when the requirements are met, they convert to true or false.

WHERE or HAVING can use the ANY and ALL operators.

ALL and ANY work with subqueries that produce numerous results.

Whether any of the subquery values satisfy the condition, ANY returns true.

If all of the subquery values fulfil the requirement, ALL returns true.

Greater than, less than, equal, and not equal comparison operators can be tweaked in innovative ways to improve comparisons made with WHERE clauses.

To match a column value to a list of results obtained from a subquery, instead of using >, which only makes sense when comparing to a single (scalar) value, you can use > ANY or > ALL.

ANY syntax:

SELECT column-names
  FROM table-name
 WHERE column-name operator ANY 
       (SELECT column-name
          FROM table-name
         WHERE condition)

ALL syntax:

SELECT column-names
  FROM table-name
 WHERE column-name operator ALL 
       (SELECT column-name
          FROM table-name
         WHERE condition)

Example 1: List all products that have sold for over $45.

SELECT ProductName AS 'Product'
  FROM Product
 WHERE Id = ANY
    (SELECT ProductId 
    FROM OrderItem 
    WHERE UnitPrice > 45)

Example 2: The comparison operator > ALL denotes a value greater than the list's MAX value. Using the preceding instance,

Sales > ALL (1000, 2000, 2500)

The comparison operator > ANY indicates that one or more items in the list are greater than another. This is the same as stating it is greater than the list's MIN value.

Sales > ANY (1000, 2000, 2500)

Example 3:

SELECT * 
FROM customers 
WHERE customer_id >= ALL (SELECT customer_id FROM orders) 
ORDER BY customer_id;

SELECT * 
FROM customers 
GROUP BY customer_id 
HAVING customer_id <> ANY (SELECT MAX(customer_id) FROM orders) 
ORDER BY customer_id;

In a WHERE clause, use the IN operator to evaluate a value to any of the items in a collection.

The ANY keyword, which must be used after a comparison operator, implies "return TRUE if the similarity is TRUE for ANY of the values in the column returned by the subquery."

To compare a value with any of the values in a list, use the ANY operator in a WHERE clause.

Before ANY, you must use the operators =, <>, <, >, <=, or >=.

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Example 1:

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Example 2: For those branches where the branch level balance is larger than zero, use pgbench accounts to get the number of accounts per branch.

Using IN Clause

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid in (SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

Using ANY Clause

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid = ANY(SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

Example 3:

For ANY Operator

SELECT *
FROM employee
WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SELECT *
FROM employee
WHERE salary IN (2000, 3000, 4000);