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
the 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)
The SQL ALL
the 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
a clause is always true. Assuming that the subquery returns one or more rows, the following table illustrates the meaning of the SQL ALL
operator:
Condition | Meaning |
---|---|
c > ALL(…) | The values in column c must be greater than the biggest value in the set to evaluate to be true. |
c >= ALL(…) | The values in column c must be greater than or equal to the biggest value in the set to evaluate to be true. |
c < ALL(…) | The values in column c must be less than the lowest value in the set to evaluate to be true. |
c >= ALL(…) | The values in column c must be less than or equal to the lowest value in the set to evaluate to be true. |
c <> ALL(…) | The values in column c must not be equal to any value in the set to evaluate to true. |
c = ALL(…) | The values in column c must be equal to any value in the set to evaluate to be true. |
SQL ALL examples
We will use the employees
table from the sample database for the demonstration:
SQL ALL with the greater than the 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);
For example, the following statement finds all employees whose salaries are greater than the highest salary of employees in the Marketing
a 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;
Let’s verify it by querying the highest salary of employees in department 2:
SELECT
MAX(salary)
FROM
employees
WHERE
department_id = 2;
This query returned 13,000
which is lower than any salary 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
an operator with the greater than or equal to the operator:
SELECT
*
FROM
table_name
WHERE
column_name >= ALL (subquery);
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;
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
the department is included in the result set.
SQL ALL with the less than operator
The following illustrates the ALL
the operator used with the less than operator:
SELECT
*
FROM
table_name
WHERE
column_name < ALL (subquery);
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;
To find all employees whose salaries are less than the lowest salary of employees in the Marketing
the department, you use the ALL
an 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;
The result is:
SQL ALL with the less than or equal to operator
The following shows the syntax of the ALL
the operator used with the less than or equal to the operator:
SELECT
*
FROM
table_name
WHERE
column_name <= ALL (subquery);
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;
SQL ALL with the not equal to operator
The following query returns all rows whose values are in the column_name
are not equal to any values returned by the subquery:
SELECT
*
FROM
table_name
WHERE
column_name <> ALL (subquery);
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;
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
an 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);
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);
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.
0 Comments
CAN FEEDBACK
Emoji