Analysis of the forgotten comparison operator modifier of SQLServer

SQLServer has three keywords that can be used to modify comparison operators: All, Any, and Some. Some and Any are equivalent.
Official reference documents
Http://technet.microsoft.com/zh-cn/library/ms187074%28SQL.90%29.aspx
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.
Copy codeThe Code is as follows:
Set nocount on
Use tempdb
Go
If (object_id ('t1') is not null) drop table t1
Create table t1 (n int)
Insert into t1 select 2 union select 3
If (object_id ('t2') is not null) drop table t2
Create table t2 (n int)
Insert into t2 select 1 union select 2 union select 3 union select 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 (select n 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 (select n 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 (select n from t1) -- 1, 2
Select * from t2 where n <> all (select n from t1) -- 1, 4
Select * from t2 where n <> any (select n from t1) -- 1, 2, 3, 4
-- Select * from t2 where n <> some (select n from t1) -- 1, 2, 3, 4
Set nocount off

Note,If T1. Because the null values of table t1 and table t2 are different from those of not exists.

For example
Select * from t2 a where not exists (select 1 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.