【Query】标量子查询 scalar subquery

目录

 

定义

---

定义

 

标量子查询是一个子查询，它只从一行中返回一个列值。标量子查询表达式的值是子查询的可选列表项的值。如果子查询返回 0 行，则标量子查询表达式的值为 NULL。如果子查询返回多行，则报错。

可以在大多数要求表达式 （expr） 的语法中使用标量子查询表达式。在所有情况下，标量子查询必须包含在其自己的括号中，即使其句法位置已将其定位在括号内（例如，当标量子查询用作内置函数的参数时）。

 

他不能用在如下条件中：

不能作为列的默认值

不能作为cluster的哈希表达式

不能用在 DML 语句的返回子句中

不能作为基于函数的索引的基础

不能用在check约束中

例

在scott用户中查询员工（emp）和部门（dept）的总数

SQL> select (select count(*) from emp) 员工总数,(select count(*) from dept) 部门总数 from dual;     员工总数   部门总数 ---------- ----------         14          4

查询每位员工的主管名字

SQL> select e.ename||q'[的主管是]'||m.ename from emp e,( select empno,ename,job from emp) m where e.mgr=m.empno;   E.ENAME||Q'[的主管是]'||M.ENAME ----------------------------------------------------------- FORD的主管是JONES SCOTT的主管是JONES TURNER的主管是BLAKE ALLEN的主管是BLAKE WARD的主管是BLAKE JAMES的主管是BLAKE MARTIN的主管是BLAKE MILLER的主管是CLARK ADAMS的主管是SCOTT BLAKE的主管是KING JONES的主管是KING CLARK的主管是KING SMITH的主管是FORD   13 rows selected.

当然也可以用自连接的方式自连接：SELF JOIN

SQL> select e.ename||q'[的主管是]'||m.ename from emp e,emp m where e.mgr=m.empno;   E.ENAME||Q'[的主管是]'||M.ENAME ----------------------------------------------------------- FORD的主管是JONES SCOTT的主管是JONES TURNER的主管是BLAKE ALLEN的主管是BLAKE WARD的主管是BLAKE JAMES的主管是BLAKE MARTIN的主管是BLAKE MILLER的主管是CLARK ADAMS的主管是SCOTT BLAKE的主管是KING JONES的主管是KING CLARK的主管是KING SMITH的主管是FORD   13 rows selected.

 

查询所有部门的名称，地点，员工数和平均工资

SQL> select dname,loc,empcount,avg_sal from (select deptno,count(deptno) empcount,avg(sal) avg_sal from emp group by deptno) e, (select deptno,dname,loc from dept) d where e.deptno=d.deptno;   DNAME           LOC               EMPCOUNT    AVG_SAL --------------- --------------- ---------- ---------- ACCOUNTING      TJ CHINA                 3 2916.66667 RESEARCH        DALLAS                   5       2175 SALES           CHICAGO                  6 1566.66667

也可用普通子查询来解决

SQL> select d.dname,d.loc,e.empcount,e.avg_sal from ( select deptno,count(deptno) empcount ,avg(sal) avg_sal from emp group by deptno) e ,dept d where e.deptno=d.deptno;   DNAME           LOC               EMPCOUNT    AVG_SAL --------------- --------------- ---------- ---------- ACCOUNTING      TJ CHINA                 3 2916.66667 RESEARCH        DALLAS                   5       2175 SALES           CHICAGO                  6 1566.66667

 

查询工资比SCOTT高的员工是谁

SQL> select ename from emp where sal>( select sal from emp where ename='SCOTT' );   ENAME ------- KING