SQL All Operator

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:

scalar_expression comparison_operator ALL ( subquery);

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.

Example Usage

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:

SELECT * FROM
products
WHERE quantity > ALL (
SELECT avg(quantity)
FROM products
);

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:

SELECT * FROM
products
WHERE quantity < ALL (
SELECT avg(quantity)
FROM products
);

In this case, the query above should return:

You can experiment with various comparison operators to discover how all operators work.

Conclusion

We hope you enjoyed this tutorial where we walked through how to use the ALL operator in SQL.

SQL All Operator

SQL ALL Operator

Example Usage

Conclusion

About the author

John Otieno