some相当于or
all相当于and
当[select sal from scott.emp where job='manager'; 结果为 sal:2975 2850 2450] 时
select emp.empno,emp.ename,emp.sal from scott.emp where sal=some(select sal from scott.emp where job='manager');
等价于
select emp.empno,emp.ename,emp.sal from scott.emp where sal=2975 or sal=2850 or sal=2450;
select emp.empno,emp.ename,emp.sal from scott.emp where sal=some(select sal from scott.emp where job='manager');
等价于
select emp.empno,emp.ename,emp.sal from scott.emp where sal=2975 or sal=2850 or sal=2450;
或者
select emp.empno,emp.ename,emp.sal from scott.emp where sal in (2975,2850,2450);
注:any与some是等价的
当[select sal from scott.emp where job='manager'; 结果为 sal:2975 2850 2450] 时
select emp.empno,emp.ename,emp.sal from scott.emp where sal>all(select sal from scott.emp where job='manager');
等价于
select emp.empno,emp.ename,emp.sal from scott.emp where sal>2975 and sal>2850 and sal>2450;
注:any与some是等价的
当[select sal from scott.emp where job='manager'; 结果为 sal:2975 2850 2450] 时
select emp.empno,emp.ename,emp.sal from scott.emp where sal>all(select sal from scott.emp where job='manager');
等价于
select emp.empno,emp.ename,emp.sal from scott.emp where sal>2975 and sal>2850 and sal>2450;
<>all ()与not in是等价的,但是=all()不等价与in,因为=不能对应多个值。
转载于:https://blog.51cto.com/smileliuyb/1132713