If the 50000 in the WHERE clause is 9999, 0 rows will be returned as 9999 will not be greater than any salary within our data. Replace the keyword SOME with the ANY keyword and it will return then same results.

ALL:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

SELECT

Name

,Salary

FROM

#davesSQLblog

WHERE

75001>ALL(SELECTSalaryFROM#davesSQLblog)

ORDERBY

SalaryDESC;

GO

Results:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Name Salary

----------------------------------------------------------------

Malory Archer75000.00

Sterling Archer50000.00

Lana Kane40150.00

Cyril Figgis35000.00

Pam Poovey30600.00

Cheryl Tunt30200.00

Barry Dillon30000.00

Ray Gillette27000.00

Dr.Krieger25500.00

Brett Bunsen25500.00

Major Nikolai Jakov22570.00

Woodhouse10000.00

(12row(s)affected)

Notice that all rows were returned because we used 75001 in the WHERE clause. If we used 75000 then 0 rows would be returned as it wouldn’t be greater than any of the salaries.

SOME, ANY and ALL don’t just need to be used in WHERE clauses, they could be used in an IF statement and possibly in a WHILE loop (one where the condition changes or it’ll be stuck looping forever ;p )

I’ve been looking around the internet to see why SOME and ANY are equivalent. I cannot find a definative answer, if you manage to find a reason then please let me know!