Just learned about these from the article "SQL Server All, Some and Any Operators" in the website www.blackwasp.co.uk. Though these things are basic, its worthy to remember again.

The SOME and ANY operators provide equivalent functionality. SOME and ANY operators return true if at least one of the generated values from the inner expression satisfies the specified condition, Where as with ALL operator, it return true if all the generated values from the inner expression satisfies the specified condition

     <pre class="brush: plain">
        declare @Emps table (EmpId int,Sal int)
        insert into @Emps values (1,100),(2,120),(3,150),(4,200)
        -- Any Returns Top most emp salary
        select * from @Emps where Sal &gt;= ALL(select Sal from @Emps)
        -- All Returns Employees except Top most salary
        select * from @Emps where Sal &lt; ANY(select Sal from @Emps)
        </pre>

</pre>

Published under: SQL Server Tips ·  ·  ·  ·