SQL ANY and ALL Operators – Tutorial with Examples

SQL ANY and ALL operators are used to compare a value with a list of values or subquery. The ANY operator returns true if any of the values in the list or subquery satisfy the condition. On the other hand, the ALL operator returns true if all of the values in the list or subquery satisfy the condition.

SQL ANY Operator

The syntax of SQL ANY operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);

In the above syntax, the “column1”, “column2”, etc. represent the columns to be retrieved from the table_name. The subquery is defined within the parentheses after the ANY keyword, and it returns the values of the specified “column_name”. The condition in the “WHERE” clause is used to filter the rows returned by the subquery. The operator can be any comparison operator such as “<“, “>”, “=”, etc.

SQL ALL Operator

The syntax of SQL ALL operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

The syntax of SQL ALL operator is similar to the SQL ANY operator. The difference is that the ALL operator returns true if all of the values in the list or subquery satisfy the condition, while the ANY operator returns true if any of the values in the list or subquery satisfy the condition.

Example of SQL ANY and ALL Operators

Consider the following table, “employees”:

ID	Name	Salary
1	John Doe	5000
2	Jane Doe	5500
3	Bob Smith	6000
4	Alice Johnson	6500

Now, if we want to find the names of all employees whose salary is greater than any salary in the list [5500, 6000, 6500], we can use the following SQL statement with the ANY operator:

SELECT Name
FROM employees
WHERE Salary > ANY (5500, 6000, 6500);

The result of the above SQL statement will be:

Name
Bob Smith
Alice Johnson

Similarly, if we want to find the names of all employees whose salary is greater than all salaries in the list [5500, 6000, 6500], we can use the following SQL statement with the ALL operator:

SELECT Name
FROM employees
WHERE Salary > ALL (5500, 6000, 6500);

The result of the above SQL statement will be:

Name
No Data Found

In this example, we can see that the SQL ANY operator returns the names of all employees whose salary is greater than any salary in the list [5500, 6000, 6500], while the SQL ALL operator returns no data as no employee has a salary greater than all the salaries in the list [5500, 6000, 6500].

In addition to this, we can also use subqueries instead of lists to compare values. For example, if we want to find the names of all employees whose salary is greater than the average salary of all employees, we can use the following SQL statement with the ALL operator:

SELECT Name
FROM employees
WHERE Salary > ALL
(SELECT AVG(Salary)
FROM employees);

The result of the above SQL statement will be:

Name
Bob Smith
Alice Johnson

In this example, we can see that the SQL ALL operator returns the names of all employees whose salary is greater than the average salary of all employees, which is calculated using the subquery in the WHERE clause.

Conclusion

In conclusion, SQL ANY and ALL operators are used to compare a value with a list of values or subquery. The ANY operator returns true if any of the values in the list or subquery satisfy the condition, while the ALL operator returns true if all of the values in the list or subquery satisfy the condition. These operators can be useful in various situations where we need to compare values and filter data based on specific conditions.