SQL ANY

Summary: in this tutorial, you will learn about the SQL ANY operator and how to use it to compare a value with a set of values.

Introduction to the SQL ANY operator

The ANY the operator is a logical operator that compares a value with a set of values returned by a subquery. The ANY operator must be preceded by a comparison operator >, >=, <, <=, =, <> and followed by a subquery.

The following illustrates the syntax of the ANY operator:

WHERE column_name comparison_operator ANY (subquery)

If the subquery returns no row, the condition evaluates to false. Suppose the subquery does not return zero rows, the following illustrates the meaning of the ANY operator when it is used with each comparison operator:

Condition	Meaning
x = ANY (…)	The values in column c must match one or more values in the set to evaluate to be true.
x != ANY (…)	The values in column c must not match one or more values in the set to evaluate to true.
x > ANY (…)	The values in column c must be greater than the smallest value in the set to evaluate to be true.
x < ANY (…)	The values in column c must be smaller than the biggest value in the set to evaluate to be true.
x >= ANY (…)	The values in column c must be greater than or equal to the smallest value in the set to evaluate to be true.
x <= ANY (…)	The values in column c must be smaller than or equal to the biggest value in the set to evaluate to be true.

SQL ANY examples

For the demonstration, we will use the employees table from the sample database:

SQL ANY with equal to operator example

The following statement uses the AVG() function and GROUP BY clause to find the average salary of each department:

SELECT 
    ROUND(AVG(salary), 2)
FROM
    employees
GROUP BY 
    department_id
ORDER BY 
    AVG(salary) DESC;

To find all employees whose salaries are equal to the average salary of their department, you use the following query:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary = ANY (
        SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY 
            department_id)
ORDER BY 
    first_name, 
    last_name,
    salary;   

Using SQL ANY with the not equal to operator example

Similarly, the following query finds all employees whose salaries are not equal to the average salary of every department:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary <> ANY (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY 
    first_name, 
    last_name, 
    salary;   

Using SQL ANY with the greater than operator example

The following query finds all employees whose salaries are greater than the average salary in every department:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary > ANY (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY 
    salary; 

Note that the lowest average salary is 4,150. The query above returns all employees whose salaries are greater than the lowest salary.

Using SQL ANY with the greater than or equal to operator example

The following statement returns all employees whose salaries are greater than or equal to the average salary in every department:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary >= ANY (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY first_name , last_name , salary; 

Using SQL ANY with the less than operator example

The following query finds all employees whose salaries are less than the average salary in every department:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary < ANY (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY salary DESC;    

In this example, employees whose salaries are smaller than the highest average salary in every department:

Using SQL ANY with the less than or equal to operator example

To find employees whose salaries are less than or equal to the average salary in every department, you use the following query:

SELECT 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary <= ANY (SELECT 
            AVG(salary)
        FROM
            employees
        GROUP BY department_id)
ORDER BY salary DESC;    

As shown in the screenshot, the result set includes the employees whose salaries are lower than or equal to the highest average salary in every department.

Now you should know how to use the SQL ANY operator to form a condition by comparing a value with a set of values.