ANY & ALL Operators

The ANY and ALL operators is used to perform a comparison between a single column value and a range of other values. Both of these operators are typically used with SELECT, WHERE and HAVING statements

The SQL ANY Operator

The ANY operator checks if any of the values matches the condition with the subquery.

In the below example, SQL interprets the subquery and get all the salaries from IT department(70k, 85k, 78k) and then compares the smallest value (70k) due to the operation and the ANY operator with the salary column in the main query. This returns the records matching the main query based on what is asked for in the SELECT statement by naming all the employees with its employee_ids for whom the salary is more than 70k.

SELECT column_name(s)
FROM table_name
WHERE column_name >/</>=/<=,<>,!= ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);
  
Example: 
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department = 'IT'

The SQL ALL Operator

The ALL operator checks if the value meets all the values using the condition in the subquery.

In the below example, the subquery will get all salaries for the IT dept. which is (70k, 85k, and 78k) and will take the highest (85k) due to the operator (> and ALL used) to compare it with all the salaries and return the ones which earn more than the highest salary in the IT dept.

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

Example: 
SELECT name, salary
FROM employees
WHERE salary > ALL 
    (SELECT salary FROM employees WHERE department = 'IT');

All syntax with WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

Difference between ANY and ALL

Feature	ANY	ALL
Condition	True if at least one value satisfies the condition	True only if all values satisfy the condition
Comparison Scope	Compares to one or more values	Compares to every single value
Example (> ANY)	Greater than at least one value in the subquery	Greater than every value in the subquery
Example (> ALL)	Greater than at least one value (like MIN())	Greater than all values (like MAX())