SQL ALL Operator

The ALL operator in SQL is used with subqueries (queries nested inside another query). It checks if *every* value returned by the subquery satisfies a condition in the main query. It's a powerful way to express complex logical relationships between tables.

ALL: Definition and Usage

ALL is particularly helpful when you need to compare a value in your main query against a set of values from a subquery. The ALL operator will return TRUE only if the condition is met for *every single* value in the subquery's result set. If even one value fails the condition, ALL returns FALSE.

Syntax

SELECT column_names
FROM table1
WHERE column_name operator ALL (SELECT column_name FROM table2 WHERE condition);
      

The operator must be a standard comparison operator (=, !=, <>, >, >=, <, <=).

Example

Checking if All Order Details Match a Condition

This query checks if all order details have a quantity of exactly 10. It will only return product names if this condition is true for *every* order detail. (Assumes 'Products' and 'OrderDetails' tables exist with a common `ProductID` column.)

SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
      

(Empty set -  It's very unlikely that all order details would have a quantity of exactly 10. Therefore, no product names will be returned.)