ANY ALL SOME的区别

干了这么长时间的ORACLE,但是真正用到ANY ALL还有SOME的并不多，而且还真的不知道有SOME这个东西，这次补一下知识

首先看一下官方文档中给的解释

SOME ANY	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to FALSE if the query returns no rows.	SELECT * FROM employees WHERE salary = ANY (SELECT salary FROM employees WHERE department_id = 30) ORDER BY employee_id;
ALL	Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. Evaluates to TRUE if the query returns no rows.	SELECT * FROM employees WHERE salary >= ALL (1400, 3000) ORDER BY employee_id;

看来SOME和ANY是一回事, ANY和ALL的区别看起来是2点

1).each value 和 every value,
从中文意思来说都是每个值,其实这个就是翻译带来的问题了，其实ANY和SOME在each value应该翻译成任意一个值 ,但是every value 对于ALL来说是应该翻译成所有值, 这下就清楚了，对于ANY和SOME是比较列表或者子查询的任意一个值，只要有满足条件即可，和OR效果一样.但是对于ALL来说，需要比较列表和子查询的所有值AND的想过一样                                                                                                                      

2).如果子查询为空的时候返回结果是不同的
当使用ANY或者SOME的时候，如果后面的子查询是空,则整个表达式返回结果是FALSE，也就一位置SELECT可能返回的结果就是空值，这个需要注意。但是如果返是ALL的话，当子查询是空的时候返回时TURE，可能不会影响色了SELECT的整体结果                                                                                                                                                                                                                                                                                                                   
链接来自：        
http://docs.oracle.com/database/121/SQLRF/conditions002.htm#SQLRF52107
                                                                                                                                                

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8747612/viewspace-1873758/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8747612/viewspace-1873758/