SQL ANY Operator Illustrated By Practical Examples

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 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)Code language: SQL (Structured Query Language) (sql)

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 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 true.
x < ANY (…)	The values in column c must be smaller than the biggest value in the set to evaluate to true.
x >= ANY (…)	The values in column c must be greater than or equal to the smallest value in the set to evaluate to true.
x <= ANY (…)	The values in column c must be smaller than or equal to the biggest value in the set to evaluate to 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;Code language: SQL (Structured Query Language) (sql)

Try it

SQL ANY - average salary of each department

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

SQL ANY with the greater than or equal to operator

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;Code language: SQL (Structured Query Language) (sql)

Try it

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;Code language: SQL (Structured Query Language) (sql)

Try it

SQL ANY with the less than or equal to operator

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 condition by comparing a value with a set of values.

Was this tutorial helpful ?