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: 

Operator Meaning
X > ALLReturn 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 < ALLReturn 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 = ALLReturn TRUE if X equals every result returned from the subquery.  If you think about it, this is a special condition. 
X <> ALLReturn 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)
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. 

Other Resources 

To learn more about SQL ALL, check out these useful resources: 

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b6728":{"name":"Main Accent","parent":-1},"03296":{"name":"Accent Low Opacity","parent":"b6728"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"b6728":{"val":"var(--tcb-skin-color-0)"},"03296":{"val":"rgba(17, 72, 95, 0.5)","hsl_parent_dependency":{"h":198,"l":0.22,"s":0.7}}},"gradients":[]},"original":{"colors":{"b6728":{"val":"rgb(47, 138, 229)","hsl":{"h":210,"s":0.77,"l":0.54,"a":1}},"03296":{"val":"rgba(47, 138, 229, 0.5)","hsl_parent_dependency":{"h":210,"s":0.77,"l":0.54,"a":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__
Name*
Email*
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"dffbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"dffbe":{"val":"var(--tcb-color-4)"}},"gradients":[]},"original":{"colors":{"dffbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Sign Up
>