oracle sql 子查询

1. =、in

在主查询执行之前,子查询(内查询)会执行一次,子查询结果被用于主查询(外查询)。

SQL> select ename from emp 
    where sal=(select max(sal) from emp);
SQL> select ename from emp 
    where sal in (select sal from emp where sal>2999);
SQL> select ename from emp where empno in ( select mgr from emp);#担任领导的员工
SQL> select ename from emp where empno not  in ( select mgr from emp);
no rows selected # 这里为空,因为子查询结果存在空值
SQL> select ename,mgr,empno from emp where mgr is null;
ENAME         MGR      EMPNO
---------- ---------- ----------
KING                7839
SQL> select ename from emp  # 不担任领导的员工 
    where empno not  in ( select mgr from emp where mgr is not null);

2. any(some)、all

  • > any 大于最小值
SQL> select ename,sal from emp where sal>any(2500,3000);
ENAME         SAL
---------- ----------
JONES        2975
BLAKE        2850
SCOTT        3000
KING         5000
FORD         3000
  • > all 大于最大值
SQL> select ename,sal from emp where sal>all(2500,3000);
ENAME         SAL
---------- ----------
KING         5000
  • < any 小于最大值
SQL> select ename,sal from emp where sal<any(2500,3000);

ENAME         SAL
---------- ----------
SMITH         800
ALLEN        1600
WARD         1250
JONES        2975
MARTIN       1250
BLAKE        2850
  • < all 小于最小值
SQL> select ename,sal from emp where sal<all(2500,3000);

ENAME         SAL
---------- ----------
SMITH         800
ALLEN        1600
WARD         1250
MARTIN       1250
CLARK        2450

SQL> select ename,sal from emp where sal<some(2500,3000); # oracle中,some等值于any

3.减少重复循环查询

  • 担任领导的员工
#同下,但循环执行多次
SQL> select ename from emp where empno in ( select mgr from emp); 
SQL> select ename from emp e where exists (select 1 from emp where mgr=e.empno);
# 1为查询mgr=e.empno结果返回的标签,查到即返回1,
# 整个语句一次全表扫描完后,返回所有为1的结果数据
  • 不担任领导的员工
SQL> select ename from emp where empno not in ( select mgr from emp where mgr is not null);
SQL> select ename from emp e where not exists (select 1 from emp where mgr=e.empno);
SQL> select ename from emp natural join (select distinct mgr empno from emp);
  • 最高薪水
SQL> select ename from emp natural join (select max(sal) sal from emp);
SQL> select ename from emp where sal=(select max(sal) from emp);

推荐阅读更多精彩内容