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.
Related Links
Sql ANY Syntax
The below syntax is used to create any operator with sub query or inner query.
SELECT column-name1, column-name2, ... column-nameN
FROM table-name
WHERE column-name operator ANY
(SELECT column-name FROM table-name WHERE condition)
- The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Sample Database Table - EmpInfo
ID | EmpName | Gender | Age | City |
---|---|---|---|---|
1 | Devi Mai | Female | 24 | Bangalore |
2 | Azaghu Varshith | Male | 23 | Kumbakonam |
3 | Geetha | Female | 21 | Mumbai |
4 | Sakunthala | Female | 29 | Hyderbhad |
5 | Pandurengan | Male | 26 | Mumbai |
6 | Vidyavathi | Female | 24 | Aruppukoottai |
SQL ANY Operator Example With Numeric Field
The following SQL statement will display records from "EmpInfo" table and It compares a value from main table to each value in a list or results from a subquery and evaluates to true if any of the subquery values meet the condition.
SELECT * FROM EmpInfo
WHERE Age > ANY (SELECT Age FROM EmpInfo WHERE ID > 4)
The sub-query returns 5 and 6. i.e, These are the ages (26, 24) for which employee "ID" is greater than "4".
Then main query is compared with this, if any of the ages in table is greater than any of the values of sub-query (i.e the ages in table should be either greater than 26 or 24) .
The result of above query is:
ID | EmpName | Gender | Age | City |
---|---|---|---|---|
3 | Sakunthala | Female | 29 | Hyderbhad |
4 | Pandurengan | Male | 26 | Mumbai |
SQL ANY Operator Example With Character Field
See below SQL statement, it contains character datatype to perform sql any operator.
SELECT * FROM EmpInfo
WHERE City = ANY (SELECT City FROM EmpInfo WHERE Age < 24)
The sub-query returns "Kumbakonam" and "Mumbai" for which employee "Age" is less than "24".
Then main query is compared with this, if any of the cities in table is matching any of the values of sub-query (i.e the cities in table should be equal to "Kumbakonam" or "Mumbai").
The result of above query is:
ID | EmpName | Gender | Age | City |
---|---|---|---|---|
2 | Azaghu Varshith | Male | 23 | Kumbakonam |
3 | Geetha | Female | 21 | Mumbai |
5 | Pandurengan | Male | 26 | Mumbai |
Related Links
SQL All vs Any
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;
SQL Any vs In
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);