SQL ANY Operator

Summary: In this tutorial, you will learn how to use the SQL ANY operator to compare a value with a set of values returned by a subquery.

Introduction to the SQL ANY operator #

The ANY the operator is used with a comparison operator such as >, >=, <, <=, =, <>  to compare a value with a list of values returned by a subquery.

Here’s the syntax of the ANY operator:

value comparison_operator ANY (subquery)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, precede the ANY operator with a comparison operator like =, >, <, >=, <=, or <>.
• Second, define a subquery that returns a list of values for comparisons.

The ANY operator returns true if the comparison is true for at least one value in the set. It returns false if the subquery returns no rows.

Typically, you use the ANY operator in the WHERE clause of the SELECT, DELETE, and UPDATE statements:

WHERE 
   column_name comparison_operator ANY(subquery)Code language: SQL (Structured Query Language) (sql)

If the subquery returns at least one row, the following table shows the meaning of the ANY operator when using with a comparison operator:

SQL ANY operator examples #

We will use the employees table from the sample database:

The following query returns the salaries of employees in department id 2:

SELECT
  salary
FROM
  employees
WHERE
  department_id = 2
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

  salary
----------
  6000.00
 13000.00Code language: plaintext (plaintext)

Using SQL ANY with = (Equal To) #

The following query uses the ANY operator with the = operator to find employees whose salary equals any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary = ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name |  salary
------------+----------
 Bruce      |  6000.00
 Pat        |  6000.00
 Michael    | 13000.00Code language: SQL (Structured Query Language) (sql)

The result includes employees whose salaries are 6,000 or 13,000.

Using SQL ANY operator with <> (Not Equal To) #

The following query uses the ANY operator with the <> operator to find employees whose salary does not equal any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary <> ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

 first_name  |  salary
-------------+----------
 Charles     |  6200.00
 Susan       |  6500.00
 Shanta      |  6500.00
 Luis        |  6900.00
 Kimberely   |  7000.00
 Ismael      |  7700.00
 Jose Manuel |  7800.00
...Code language: SQL (Structured Query Language) (sql)

The result set does not include employees with salaries that are 6,000 and 13,000.

Using SQL ANY operator with > (Greater Than) #

The following query uses the ANY operator with the > operator to find employees whose salary is greater than any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary > ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  salary
-------------+----------
 Charles     |  6200.00
 Susan       |  6500.00
 Shanta      |  6500.00
 Luis        |  6900.00
 Kimberely   |  7000.00
...Code language: SQL (Structured Query Language) (sql)

The query returns employees with salaries greater than the lowest salary in department 2, which is 6,000.

Using SQL ANY Operator with >= (Greater Than or Equal To) #

The following query uses the ANY operator with the >= operator to find employees with a salary greater than or equal to any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary >= ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary;Code language: SQL (Structured Query Language) (sql)

Try it

 first_name  |  salary
-------------+----------
 Pat         |  6000.00
 Bruce       |  6000.00
 Charles     |  6200.00
 Shanta      |  6500.00
 Susan       |  6500.00
 Luis        |  6900.00
 ...Code language: SQL (Structured Query Language) (sql)

The query returns employees with a salary greater than or equal to (>=) the lowest salary in the department 2, which is 6,000.

Using SQL ANY operator with < (Less Than) #

The following query uses the ANY operator with the < operator to find employees whose salary is less than any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary < ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

first_name  |  salary
-------------+----------
 Shelley     | 12000.00
 Nancy       | 12000.00
 Den         | 11000.00
 Hermann     | 10000.00
 Daniel      |  9000.00
...Code language: SQL (Structured Query Language) (sql)

The result set includes the employees with the salaries lower than the highest salary (13m000) of the employees in the department 2.

Using SQL ANY operator with <= (Less Than or Equal To) #

The following query uses the ANY operator with the <= operator to find employees whose salary is less than or equal to any salary of employees in the department id 2:

SELECT
  first_name,
  salary
FROM
  employees
WHERE
  salary <= ANY (
    SELECT
      salary
    FROM
      employees
    WHERE
      department_id = 2
  )
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

 first_name  |  salary
-------------+----------
 Michael     | 13000.00
 Nancy       | 12000.00
 Shelley     | 12000.00
 Den         | 11000.00
 Hermann     | 10000.00
....Code language: SQL (Structured Query Language) (sql)

The result set includes the employees with salaries less than or equal to the highest salary (13,000) of the employees in the department 2.

Summary

• Use theANY operator with a comparison operator to compare a value with a set of values returned by a subquery.

Quiz #

Databases #

• PostgreSQL ANY Operator
• Oracle ANY Operator