ANY比较符用法

来源：互联网发布：ubuntu怎么复制文件夹编辑：程序博客网时间：2024/06/06 07:29

ANY

SOME

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;

官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions002.htm#sthref1908

以scott/tiger 的emp表作为示例：

scott@TEST0924> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

ANY：类似于OR操作符。大于任何值即大于最小的那个值

scott@TEST0924> SELECT empno, sal

2 FROM emp

3 WHERE sal > ANY (2000, 3000);

EMPNO SAL

---------- ----------

7566 2975

7698 2850

7782 2450

7788 3000

7839 5000

7902 3000

6 rows selected.

等价于大于任何值

scott@TEST0924> SELECT empno, sal

2 FROM emp

3 WHERE sal > 2000 or sal>3000

4 ;

EMPNO SAL

---------- ----------

7566 2975

7698 2850

7782 2450

7788 3000

7839 5000

7902 3000

6 rows selected.

等价于大于最小值

scott@TEST0924> SELECT empno, sal

2 FROM emp

3 WHERE sal > 2000 ;

EMPNO SAL

---------- ----------

7566 2975

7698 2850

7782 2450

7788 3000

7839 5000

7902 3000

6 rows selected.

如果any后面跟的是子查询

scott@TEST0924> SELECT e2.sal

2 FROM emp e2

3 WHERE e2.deptno = 10;

SAL

----------

2450

5000

1300

scott@TEST0924> SELECT e1.empno, e1.sal

2 FROM emp e1

3 WHERE e1.sal > ANY (SELECT e2.sal

4 FROM emp e2

5 WHERE e2.deptno = 10);

EMPNO SAL

---------- ----------

7839 5000

7902 3000

7788 3000

7566 2975

7698 2850

7782 2450

7499 1600

7844 1500

8 rows selected.

即等价于大于最小值

scott@TEST0924> SELECT e1.empno, e1.sal

2 FROM emp e1

3 WHERE e1.sal >1300

4 ;

也等价与

scott@TEST0924> SELECT e1.empno, e1.sal

2 FROM emp e1

3 WHERE EXISTS (SELECT e2.sal

4 FROM emp e2

5 WHERE e2.deptno = 10

6 AND e1.sal > e2.sal);

EMPNO SAL

---------- ----------

7839 5000

7902 3000

7788 3000

7566 2975

7698 2850

7782 2450

7499 1600

7844 1500

8 rows selected.

如果any后面的子查询返回非0行，则

"x = ANY (...)": The value must match one or more values in the list to evaluate to TRUE.至少匹配一个值
"x != ANY (...)": The value must not match one or more values in the list to evaluate to TRUE.一个值都不匹配
"x > ANY (...)": The value must be greater than the smallest value in the list to evaluate to TRUE.大于最小值
"x < ANY (...)": The value must be smaller than the biggest value in the list to evaluate to TRUE.小于最大值
"x >= ANY (...)": The value must be greater than or equal to the smallest value in the list to evaluate to TRUE.大于等于最小值
"x <= ANY (...)": The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE.小于等于最大值

如果any后面的子查询返回为0行

此查询没有返回值

scott@TEST0924> SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;

no rows selected

则以下any都没有返回值