SQL ALL Operator
The SQL ALL Operator
The ALL operator allows you to perform a comparison between a single
column value and a range of other values.
The ALL operator returns TRUE if all values in the subquery result-set meet the condition.
The ALL operator is used with
SELECT,
WHERE and HAVING statements.
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE
condition;ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE
column_name operator ALL (subquery);Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Chais | 18.00 |
| 2 | Chang | 19.00 |
| 3 | Aniseed Syrup | 10.00 |
| 4 | Chef Anton's Cajun Seasoning | 22.00 |
And a selection from the "OrderDetails" table:
| OrderDetailID | ProductID | Quantity |
|---|---|---|
| 1 | 11 | 12 |
| 2 | 42 | 10 |
| 3 | 72 | 5 |
| 4 | 14 | 9 |
SQL ALL Examples
The following SQL statement lists ALL the product names:
The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):
Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
Try it Yourself »
