SQL ALL Operator

Summary: in this tutorial, you will learn how to use the SQL ALL operator to compare a value with all values returned by a subquery.

Introduction to the SQL ALL operator #

The ALL operator is used with a comparison operator such as >, >=, <, <=, <>, = to compare a value with all values returned by a subquery.

Here’s the basic syntax of the ALL operator:

value ALL comparison_operator (subquery)

The condition is true when:

• The subquery returns no row.
• Or the comparison of the value with all the values returned by the subquery are true.

The following shows the syntax when using the ALL operator in a WHERE clause of a SELECT statement:

SELECT
  column1,
  column2
FROM
  table_name
WHERE
  column_name comparison_operator ALL (subquery);Code language: SQL (Structured Query Language) (sql)

Note that you can use ALL operator in the WHERE clause of other statements, such as DELETE and UPDATE.

The ALL operator ensures that the condition in the WHERE clause must be true for all values returned by the subquery.

Here are the main characteristics of the ALL operator:

• The subquery must return a result set with one column containing a list of values for comparison.
• The ALL operator compares every value in the column_name with all values returned by a subquery.
• If the subquery returns no row, the result is always true.
• If the subquery returns at least one row, the following table shows the meaning of the ALL operator for each comparison operator:

SQL ALL operator examples #

We’ll use the employees table from the sample database:

The following query returns the minimum and maximum salaries of the employees in the department with id 3:

SELECT
  MIN(salary) min_salary,
  MAX(salary) max_salary
FROM
  employees
WHERE
  department_id = 3;

Try it

Output:

 min_salary | max_salary
------------+------------
    2500.00 |   11000.00

SQL ALL operator with the greater than operator #

The following statement uses the ALL operator with the greater than (>) operator to find employees with salaries higher than the average salaries of employees in the department id 3:

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

Try it

The query returns the employees with salaries of 12,000 and more which are greater than the highest salary of all employees in the department 3:

first_name | last_name |  salary
------------+-----------+----------
 Shelley    | Higgins   | 12000.00
 Nancy      | Greenberg | 12000.00
 Michael    | Hartstein | 13000.00
 Karen      | Partners  | 13500.00
 John       | Russell   | 14000.00
 Lex        | De Haan   | 17000.00
 Neena      | Kochhar   | 17000.00
 Steven     | King      | 24000.00

SQL ALL operator with the greater than or equal to operator #

The following query uses the ALL operator with the greater than or equal to operator (>=) to find employees with salaries higher than or equal to the highest salary of employees in the department 3:

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

Try it

Output:

 first_name | last_name |  salary
------------+-----------+----------
 Den        | Raphaely  | 11000.00
 Shelley    | Higgins   | 12000.00
 Nancy      | Greenberg | 12000.00
 Michael    | Hartstein | 13000.00
 Karen      | Partners  | 13500.00
 John       | Russell   | 14000.00
 Neena      | Kochhar   | 17000.00
 Lex        | De Haan   | 17000.00
 Steven     | King      | 24000.00

The result set includes the employee with the salary 11,000 and more which is greater than or equal to the highest salary of the department 3 (11,000).

SQL ALL operator with the less than operator #

The following query uses the ALL operator with the < operator to find employees with salaries less than the average salary of all departments:

SELECT
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  salary < ALL (
    SELECT
      average_salary
    FROM
      (
        SELECT
          AVG(salary) average_salary,
          department_id
        FROM
          employees
        GROUP BY
          department_id
      )
  )
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name |  last_name  | salary
------------+-------------+---------
 Sarah      | Bell        | 4000.00
 Britney    | Everett     | 3900.00
 Alexander  | Khoo        | 3100.00
 Shelli     | Baida       | 2900.00
 Sigal      | Tobias      | 2800.00
 Irene      | Mikkilineni | 2700.00
 Guy        | Himuro      | 2600.00
 Karen      | Colmenares  | 2500.00

How the query works.

First, the subquery returns a list of the average salaries of all departments:

SELECT
  average_salary
FROM
  (
    SELECT
      AVG(salary) average_salary,
      department_id
    FROM
      employees
    GROUP BY
      department_id
  )

Second, the outer query returns employees with salaries less than all the average salaries returned by the subquery.

SQL ALL operator with the less than or equal to operator #

The following statement uses the ALL operator with the less than or equal to the operator (<=) to find employees with salaries are less than or equal to the highest salaries of all departments:

SELECT
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  salary <= ALL (
    SELECT
      max_salary
    FROM
      (
        SELECT
          MAX(salary) max_salary,
          department_id
        FROM
          employees
        GROUP BY
          department_id
      )
  )
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name |  last_name  | salary
------------+-------------+---------
 Jennifer   | Whalen      | 4400.00
 Diana      | Lorentz     | 4200.00
 Sarah      | Bell        | 4000.00
 Britney    | Everett     | 3900.00
 Alexander  | Khoo        | 3100.00
 Shelli     | Baida       | 2900.00
 Sigal      | Tobias      | 2800.00
 Irene      | Mikkilineni | 2700.00
 Guy        | Himuro      | 2600.00
 Karen      | Colmenares  | 2500.00

SQL ALL operator with the not equal to operator #

The following query uses the ALL operator the operator (<>) to find employees with salaries not equal to the lowest salaries of every department:

SELECT
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  salary <> ALL (
    SELECT
      min_salary
    FROM
      (
        SELECT
          MIN(salary) min_salary,
          department_id
        FROM
          employees
        GROUP BY
          department_id
      )
  )
ORDER BY
  salary DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | last_name  |  salary
-------------+------------+----------
 Steven      | King       | 24000.00
 John        | Russell    | 14000.00
 Karen       | Partners   | 13500.00
 Michael     | Hartstein  | 13000.00
 Shelley     | Higgins    | 12000.00
 Nancy       | Greenberg  | 12000.00
...

Summary #

• Use the ALL operator with a comparison operator to compare a value with all values returned by a subquery.

Quiz #

Databases #

• PostgreSQL ALL Operator
• Oracle ALL Operator
• SQL Server ALL Operator