The ALL operator in SQL compares a scalar value with the result of a query that returns a single value column.
Let us discuss how to use the ALL operator in SQL.
SQL ALL Operator
The syntax is as illustrated below:
A valid comparison operator must precede the ALL operator. For example, you can use comparison operators such as greater than, less than, greater than or equal to, etc.
The query used must return a single value column.
Ensure that the data type of the subquery matches the data type of the scalar expression.
Assume we have a table as shown:
Table name: products
We can use the ALL operator with a query as shown in the example below:
WHERE quantity > ALL (
In the query above, we fetch the products whose quantity is greater than the average of all quantities.
The code above should return:
You can also perform the above operation using a less than comparison operator as shown:
WHERE quantity < ALL (
In this case, the query above should return:
You can experiment with various comparison operators to discover how all operators work.
We hope you enjoyed this tutorial where we walked through how to use the ALL operator in SQL.