Use the SQL ALL Operator with a subquery to compare one value to ALL values returned from the subquery. If value returned from the SQL subquery satisfies the comparison’s condition, the comparison returns TRUE.
Unlike the ANY, to satisfy ALL, every value must meet the condition.
Note: This article speaks to ALL being used withing a subquery. ALL can also be used with UNION and SELECT.
SQL ALL Operator Syntax
Here is the general syntax for ALL:
SELECT column1, column2, … FROM table 1 WHERE column2 > ALL (subquery1)
Though we use greater than in our example above (>), you can substitute it for ALL valid comparison operators. Other examples include:
|X > ALL||Return TRUE if X is greater than ALL results returned from the subquery.|
|X >= ALL||Return TRUE if X is greater than or equal to ALL results returned from the subquery.|
|X < ALL||Return TRUE if X is less than ALL results returned from the subquery.|
|X <= ALL||Return TRUE if X is less than or equal to ALL results returned from the subquery.|
|X = ALL||Return TRUE if X equals every result returned from the subquery. If you think about it, this is a special condition.|
|X <> ALL||Return TRUE if X does not equal every result returned from the subquery.|
SQL ALL Operator Example
Here we are returning every sales person who has a bonus larger than the bonuses of the sales people who year-to-date sales were less than $1,000,000.
Try it for yourself.
SELECT p.BusinessEntityID, p.FirstName, p.LastName, s.Bonus, s.SalesYTD FROM Person.Person AS p INNER JOIN Sales.SalesPerson AS s ON p.BusinessEntityID = s.BusinessEntityID WHERE s.Bonus > ALL (SELECT Bonus FROM Sales.SalesPerson WHERE Sales.SalesPerson.SalesYTD < 1000000)
The subsquery returns a list of bonuses for sales persons less than $1,000,000 in year to date sales. Then that list use used with the ALL operator.
Keep in mind that for the result to return rows, each sales’ persons bonus is greater than every bonus returned from the subquery.
To learn more about SQL ALL, check out these useful resources: