Use and difference of All, Any, and Some in sqlserver

SQLServer has three keywords that can be used to modify comparison operators: All, Any, and Some. Some and Any are equivalent. They act between comparison operators and subqueries. They act on classes such as Exists, notexists, in, notin, and other logical meanings. These syntaxes are also supported by SQLServer2000, but few users use them.

SQLServer has three keywords that can be used to modify comparison operators: All, Any, and Some. Some and Any are equivalent. They act on the comparison operator and subquery, and function classes Exists, not exists, in, not in, and other logical meanings. These syntaxes are also supported by SQLServer2000, but few users see it.

SQLServer has three keywords that can be used to modify comparison operators: All, Any, and Some. Some and Any are equivalent.

They act between comparison operators and subqueries, and are similar to Exists, not exists, in, not in, and other logical meanings. These syntaxes are also supported by SQLServer2000, but few users can use them.

Official reference: http://technet.microsoft.com/zh-cn/library/ms187074%28SQL.90%29.aspx (recommended)

Set nocount on use tempdbgo if (object_id ('t1') is not null) drop table t1create table t1 (n int) insert into t1 select 2 unionselect 3 if (object_id ('t2') is not null) drop table t2create table t2 (n int) insert into t2 select 1 unionselect 2 union select 3 union select 4 -- t1 table data 2 and 3 -- t2 table data 1, 2, 3, 4 -- '> all' indicates: the data of column n in Table t2 is greater than that of column n in Table t1. The result is only 4. select * from t2 where n> all (select n from t1) -- 4 select * from t2 where n> any (select n from t1) -- 3,4 select * from t2 where n> some (selectn from t1) -- 3,4 select * from t2 where n = all (select n from t1) -- no data select * from t2 where n = any (select n from t1) -- 2, 3 select * from t2 where n = some (selectn from t1) -- 2, 3 select * from t2 where n <all (select n from t1) -- 1 select * from t2 where n <any (select n from t1) -- 1, 2 select * from t2 where n <some (selectn from t1) -- 1, 2 select * from t2 where n <> all (select n from t1) -- select * from t2 where n <> any (select n from t1) --, select * from t2 where n <> some (select n from t1, 3, 4, set nocount off

Note: 1. = any is equivalent to in.
2. If t1 contains null data, All comparison operations related to All will not return any results. Because the null values of table t1 and table t2 exist, they have some comparison characters such as notexists.Differences.
For example

select * from t2 a where not exists(select1 from t1 where n>=a.n) select * from t2 where  n > all(select n from t1) 

They are logically similar, but the processing of null is exactly the opposite. The first sentence will ignore the null of the subquery and check the null of t2 at the same time, the second sentence ignores the null value of t2 and the data cannot be queried because of the null value in t1.