SQL ALL

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

Introduction to the SQL ALL operator

The SQL ALL operator is a logical operator that compares a single value with a single-column set of values returned by a subquery.

The following illustrates the syntax of the SQL ALL operator:

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

The SQL ALL operator must be preceded by a comparison operator such as >, >=, <, <=, <>, = and followed by a subquery. Some database systems such as Oracle allow a list of literal values instead of a subquery.

Note that if the subquery returns no row, the condition in the WHERE clause is always true. Assuming that the subquery returns one or more rows, the following table illustrates the meaning of the SQL ALL operator:

SQL ALL examples

We will use the employees table from the sample database for the demonstration:

SQL ALL with the greater than operator

The following query finds rows whose values in the column_name are greater than the biggest values returned by the subquery:

SELECT 
    *
FROM
    table_name
WHERE
    column_name > ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

For example, the following statement finds all employees whose salaries are greater than the highest salary of employees in the Marketing department whose id is 2:

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

Let’s verify it by querying the highest salary of employees in department 2:

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

This query returned 13,000 which is lower than any salary that returned by the query which used the ALL operator above.

SQL ALL with the greater than or equal to operator

The following shows the syntax of the SQL ALL operator with the greater than or equal to operator:

SELECT 
    *
FROM
    table_name
WHERE
    column_name >= ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

The query returns all rows whose values in the column_name are greater than or equal to all the values returned by the subquery.

For example, the following query finds all employees whose salaries are greater than or equal to the highest salary of employees in the Marketing department:

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

As shown clearly in the screenshot, the salary of Michael is 13,000 which is equal to the highest salary of employees in the Marketing department is included in the result set.

SQL ALL with the less than operator

The following illustrates the ALL operator used with the less than operator:

SELECT 
    *
FROM
    table_name
WHERE
    column_name < ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

This query returns all rows whose values in the column_name are smaller than the smallest values returned by the subquery.

The following statement finds the lowest salary of employees in the Marketing department:

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

To find all employees whose salaries are less than the lowest salary of employees in the Marketing department, you use the ALL operator with the less than operator as follows:

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

The result is:

SQL ALL with the less than or equal to operator

The following shows the syntax of the ALL operator used with the less than or equal to operator:

SELECT 
    *
FROM
    table_name
WHERE
    column_name <= ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

For example, the following statement finds all employees whose salaries are less than or equal to the lowest salary of employees in the Marketing department:

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

SQL ALL with the not equal to operator

The following query returns all rows whose values in the column_name are not equal to any values returned by the subquery:

SELECT 
    *
FROM
    table_name
WHERE
    column_name <> ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

For example, to find employees whose salaries are not equal to the average salary of every department, you use the query below:

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

Notice that the subquery finds the average salary of employees by the department by using the AVG() function and the GROUP BY clause.

SQL ALL with the equal to operator

When you use the ALL operator with the equal to operator, the query finds all rows whose values in the column_name are equal to any values returned by the subquery:

SELECT 
    *
FROM
    table_name
WHERE
    column_name = ALL (subquery);
Code language: SQL (Structured Query Language) (sql)

The following example finds all employees whose salaries are equal to the highest salary of employees in the Marketing department:

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary = ALL (SELECT 
            MAX(salary)
        FROM
            employees
        WHERE
            department_id = 2);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL ALL operator to test whether a value matches a set of values returned by a subquery.