The SOME, ANY and ALL are logical operators that compare against a scalar value. This article is a simple overview of how they work.
Sample Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
-- ARCHER TV Series sample data -- 1. Check to see if the table exists IF OBJECT_ID('tempdb..#davesSQLblog') IS NOT NULL BEGIN -- 2. If it does then drop it DROP TABLE #davesSQLblog END; GO -- 3. Create the table CREATE TABLE #davesSQLblog ( RecordID INT IDENTITY(1,1) CONSTRAINT PK_RecordID PRIMARY KEY CLUSTERED , Name VARCHAR(25) NOT NULL , Salary DECIMAL(7,2) NOT NULL , StartDate DATETIME NOT NULL CONSTRAINT DF_StartDate DEFAULT (GETDATE()) , VacationHours AS (DATEDIFF(DAY,StartDate,GETDATE()) * 0.125) ); GO -- 4. Dont want to show all the records being inserted. SET NOCOUNT ON; -- 5. Insert our sample data INSERT INTO #davesSQLblog VALUES ('Sterling Archer', 50000, GETDATE() -405 ); INSERT INTO #davesSQLblog VALUES ('Lana Kane', 40150, GETDATE() -403); INSERT INTO #davesSQLblog VALUES ('Cheryl Tunt', 30200, GETDATE() -150); INSERT INTO #davesSQLblog VALUES ('Pam Poovey', 30600, GETDATE() -200); INSERT INTO #davesSQLblog VALUES ('Ray Gillette', 27000, GETDATE() -372); INSERT INTO #davesSQLblog VALUES ('Dr. Krieger', 25500, GETDATE() -350); INSERT INTO #davesSQLblog VALUES ('Woodhouse', 10000, DEFAULT); INSERT INTO #davesSQLblog VALUES ('Barry Dillon', 30000, GETDATE() -230); INSERT INTO #davesSQLblog VALUES ('Brett Bunsen', 25500, DEFAULT); INSERT INTO #davesSQLblog VALUES ('Cyril Figgis', 35000, GETDATE() -400); INSERT INTO #davesSQLblog VALUES ('Malory Archer', 75000, GETDATE() -802); INSERT INTO #davesSQLblog VALUES ('Major Nikolai Jakov', 22570, GETDATE() -900); -- 6. Turn it back on! SET NOCOUNT OFF; -- 7. Check our data. SELECT * FROM #davesSQLblog |
SOME and ANY:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Name , Salary FROM #davesSQLblog WHERE 50000 > SOME(SELECT Salary FROM #davesSQLblog) ORDER BY Salary DESC; GO |
Results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Name Salary ------------------------- --------------------------------------- Malory Archer 75000.00 Sterling Archer 50000.00 Lana Kane 40150.00 Cyril Figgis 35000.00 Pam Poovey 30600.00 Cheryl Tunt 30200.00 Barry Dillon 30000.00 Ray Gillette 27000.00 Dr. Krieger 25500.00 Brett Bunsen 25500.00 Nikolai Jakov 22570.00 Woodhouse 10000.00 (12 row(s) affected) |
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:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Name , Salary FROM #davesSQLblog WHERE 75001 > ALL(SELECT Salary FROM #davesSQLblog) ORDER BY Salary DESC; GO |
Results:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Name Salary ------------------------- --------------------------------------- Malory Archer 75000.00 Sterling Archer 50000.00 Lana Kane 40150.00 Cyril Figgis 35000.00 Pam Poovey 30600.00 Cheryl Tunt 30200.00 Barry Dillon 30000.00 Ray Gillette 27000.00 Dr. Krieger 25500.00 Brett Bunsen 25500.00 Major Nikolai Jakov 22570.00 Woodhouse 10000.00 (12 row(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!
Leave a Comment