SQL ALL Vs ANY Operator

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

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

Only subqueries that return one row employ comparison operators (=, >,<) With the ANY and ALL operators in SQL Subqueries, you can compare subqueries that return multiple rows.

ANY and ALL check whether the values returned by a subquery match the left-hand expression in some or all cases.

It works in the same way as the SOME and IN operators and must be followed by a comparison operator.

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

SQL ALL Operator

The comparison operator > ALL denotes that a value is greater than the list's MAX value.

Example: Using the ALL operator, you may find out if an employee's salary is equivalent to a certain amount.

Let's look at the "Employee" table for records.

id	name	dept	salary
1	Harish Karthik	Programming	14000
2	Balan	Administration	33000
3	Ramanathan	Programming	12500
4	Ranjani Mai	Web Design	33500

Using the ALL operator, a query for locating an employee with a salary of 15000 will be:

--Mysql, Sql-server, Ms access

SELECT Name,Salary
FROM Employee
WHERE Salary > ALL(SELECT Salary FROM Employees WHERE Salary < 15000);

Output: The result of above query is,

name	salary
Balan	33000.00
Ranjani Mai	33500.00

SQL ANY Operator

The comparison operator ANY denotes that one or more items in the list are greater than another.

It is the same as declaring it is greater than the list's MIN value. As a result, the expression.

When the ANY keyword is used in a subquery, it returns true if any value returned in the subquery equals the value of the left-hand expression.

Example: Using ANY operator, locate any employee wage that is less than given number.

Let's look at the "Employee" table for records.

id	name	gender	city	dept	salary
1	Padmavathi	Female	Madurai	Database	35000
2	Nirmala	Female	Mumbai	Programming	5500
3	Vinoth Kumar	Male	Delhi	Web Design	7000
4	Siva Kumar	Male	Mysore	Database	18500

For finding any employee salary less than 15000 using ANY operator, a query will be:

SELECT Name,Salary
FROM Employee
WHERE Salary = ANY (SELECT Salary FROM Employee  WHERE Salary < 15000);

Output: The result will be

name	salary
Nirmala	5500
Vinoth Kumar	7000

SQL Difference Between ANY And ALL Operator

There are 6 main contrasts in ANY Vs ALL operator:-

ANY	ALL
If the comparison is TRUE for ANY of the values returned by the subquery, the ANY operator returns TRUE.	If the comparison is TRUE for ALL of the data returned by the subquery, the ALL operator returns TRUE.
With a `WHERE` or `HAVING` clause, the ANY operator can be utilised.	With a WHERE or HAVING clause, the ALL operator can be utilised.
If the ANY parameter is supplied, however, the outcome is FALSE.	The result is TRUE if the subquery's result set contains no rows with the ALL parameter given.
The truth value is equal to TRUE if the ANY parameter is given and the comparison of the expression value with at least one value retrieved from the subquery returns TRUE.	The truth value is FALSE if the ALL parameter is given and a comparison of the expression value with at least one value retrieved from the subquery returns FALSE.
The truth value is also FALSE if the ANY argument is given and each comparison of the expression value with the data returned from the subquery returns FALSE.	The truth value is also TRUE if the ALL argument is supplied and each comparison of the expression value with the data returned from the subquery returns TRUE.
The significance of the ANY operator and the semantic meaning of the ALL operator are readily mistaken, and vice versa.	The significance of the ALL operator is easy to understand when compared to the semantic meaning of the ANY operator, and vice versa.

Note:-

Use ANY and ALL operators conservatively! The EXISTS function, which is detailed later in this chapter , can be used to improve any query that uses ANY or ALL.

All Tutorials