Oracle ALL, ANY and SOME比较

ALL

The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.

When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together withAND operators

比较对象:一个值;一系列值或是子查询

位置:前 : =, !=, >, <, <=, >= ; 后 : 一系列值或子查询;

当比较对象是一系列值时,等价于 and连接这一系列值的分个比较;

SELECT empno, salFROM   empWHERE  sal > ALL (2000, 3000, 4000);-- Transformed to equivalent statement without ALL.SELECT empno, salFROM   empWHERE  sal > 2000 AND sal > 3000 AND sal > 4000;

当比较对象是子查询时,会先转译为any,然后转译为exists进行查询.并且子查询返回0行时,取比较结果为TRUE.

SELECT e1.empno, e1.salFROM   emp e1WHERE  e1.sal > ALL (SELECT e2.sal                     FROM   emp e2                     WHERE  e2.deptno = 20); -- Transformed to equivalent statement using ANY.SELECT e1.empno, e1.salFROM   emp e1WHERE  NOT (e1.sal <= ANY (SELECT e2.sal                           FROM emp e2                           WHERE e2.deptno = 20)); -- Transformed to equivalent statement without ANY.SELECT e1.empno, e1.salFROM   emp e1WHERE  NOT EXISTS (SELECT e2.sal                   FROM emp e2                   WHERE e2.deptno = 20                   AND   e1.sal <= e2.sal);

ANY

The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery.

When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together withOR operators.

比较对象:一个值;一系列值或是子查询

位置:前 : =, !=, >, <, <=, >= ; 后 : 一系列值或子查询;

当比较对象是一系列值时,等价于 or 连接这一系列值的分个比较;

SELECT empno, salFROM   empWHERE  sal > ANY (2000, 3000, 4000);-- Transformed to equivalent statement without ANY.SELECT empno, salFROM   empWHERE  sal > 2000 OR sal > 3000 OR sal > 4000;

当比较对象是子查询时,会转译为exists进行查询.并且子查询返回0行时,取比较结果为False.

SELECT e1.empno, e1.salFROM   emp e1WHERE  e1.sal > ANY (SELECT e2.sal                     FROM   emp e2                     WHERE  e2.deptno = 10); -- Transformed to equivalent statement without ANY.SELECT e1.empno, e1.salFROM   emp e1WHERE  EXISTS (SELECT e2.sal               FROM emp e2               WHERE e2.deptno = 10               AND   e1.sal > e2.sal);

SOME

The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.

some

some和any是一致的,二者可以相互转化;