Oracle sql 中的 ALL,ANY,SOME

[转自] http://www.itpub.net/thread-1355835-1-1.html

any和some是等价的，其与all的前面都只能是比较符号，即=, !=, >, <, <=, >=；其与all的后面都只能是子查询或者一串值

any的含义是将一个值与any后的每一个值去比较，满足任何一个比较条件即为true

all的含义是将一个值与all后的每一个值去比较，满足全部比较条件才为true

CREATE TABLE tmp_test(ID number,txt VARCHAR2(10));
 CREATE table tmp_test_his AS SELECT * from tmp_test WHERE 1=2;
 INSERT INTO tmp_test VALUES(1,'a');
 INSERT INTO tmp_test VALUES(2,'b');
 INSERT INTO tmp_test VALUES(3,'c');
 INSERT INTO tmp_test VALUES(4,'d');
 INSERT into tmp_test_his VALUES(2,'b');
 INSERT into tmp_test_his VALUES(3,'c');
 INSERT into tmp_test_his VALUES(4,'x');

select a.* FROM tmp_test_his a where (a.id,a.txt) = all(select b.id,b.txt  from tmp_test b where a.id=b.id and a.txt=b.txt);
        ID TXT      
---------- ----------
         2 b        
         3 c        
         4 x        

3 rows selected.

select b.id,b.txt from tmp_test b,tmp_test_his a where a.id=b.id and a.txt=b.txt;

ID TXT
---------- ----------
2 b
3 c

2 rows selected.

select a.* FROM tmp_test_his a where (a.id,a.txt) = all(select b.id,b.txt  from tmp_test b, tmp_test_his a where a.id=b.id and a.txt=b.txt);

no rows selected.

 select a.* FROM tmp_test_his a where (a.id,a.txt) = any(select b.id,b.txt  from tmp_test b, tmp_test_his a where a.id=b.id and a.txt=b.txt);


        ID TXT      
---------- ----------
         2 b        
         3 c   
2 rows selected.