Oracle SQL 学习笔记4- 子查询
2020-02-04 本文已影响0人
赵阳_c149
子查询可以解决的问题类型
在实际编写sql的过程中,我们往往会遇到一些比较复杂的sql场景,这个时候,我们需要用到子查询。
比如，当一查询基于未知值时，可以在一个SELECT语句的FROM子句或者WHERE子句中使用子查询。
定义子查询
SELECT select_list
FORM table
WHERE expr operator
( SELECT select_list
FROM table);
在主查询执行之前，子查询首先要执行一次，其结果要在主查询中使用。
子查询要点
- 子查询要包括在括号里面
- 子查询要放在比较操作符的右边
- 子查询中不要包含ORDER BY 子句
子查询不能用ORDER BY，因为在sql执行时到ORDER BY这句返回的是指针, 并没有真正返回tmp table。如果有top n 的话, 可以有ORDER BY。【1】 - 对于单行的子查询使用单行的比较操作符
- 对于多行的子查询使用多行的比较操作符
子查询的类型
- 单行子查询
- 多行子查询
- 多列子查询
单行子查询
单行子查询仅返回一行。可以使用的比较操作符包括：
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于或者等于 |
| < | 小于 |
| <= | 小于或者等于 |
| <> | 不等于 |
在子查询中使用分组函数
group_func.JPG
在子查询中使用HAVING子句
在子查询中使用HAVING子句，Oracle服务器首先执行子查询，然后将结果返回给主查询的HAVING子句。
having.JPG
多行子查询
多行子查询返回值多于一行，使用的比较操作符包括：
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的某一个值 |
| ANY | 与列表中的任意值比较 |
| ALL | 与列表中的所有值比较 |
错误实例：
SQL> SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno);
SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
在多行子查询中使用Any操作符
SELECT empno, ename
FROM emp
WHERE sal < ANY
(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10;
使用多列子查询
多列子查询又可以分为成对列比较和非成对列比较。
成对列比较
找出与第605号订单的产品号prodid、产品数量qty相匹配的订单的ordid、prodid和qty。
SELECT ordid, prodid, qty
FROM item
WHERE (prodid, qty) IN
(SELECT prodid, qty
FROM item
WHERE ordid = 605)
AND ordid <> 605;
非成对列比较
找出与第605号订单的产品号prodid或者产品数量qty两者中的任何一个相匹配的订单的ordid、prodid和qty。
SELECT ordid, prodid, qty
FROM item
WHERE prodid IN
(SELECT prodid
FROM item
WHERE ordid = 605)
AND qty IN
(SELECT qty
FROM item
WHERE ordid = 605)
AND ordid <> 605;
当子查询查到空值时，其处理方法是什么
SQL> SELECT empployee.ename
FROM emp empployee
WHERE empployee.empno NOT IN
(SELECT manager.mgr FROM emp manager);
no rows selected
其中子查询的结果包含null，主查询中也含有null，所以查询结果为空。
FROM子句中的子查询
SQL> SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno, avg(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;
ENAME SAL DEPTNO SALAVG
-------------------- ---------- ---------- ----------
WARD 1650 30 1300
TURNER 1600 30 1300
ALLEN 1500 30 1300
MARTIN 1400 30 1300
FORD 1700 20 1366.66667
CLERK 750 10 675
6 rows selected.
