Oracle SQL 学习笔记4- 子查询

子查询可以解决的问题类型

在实际编写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.

【1】SQL的子查询可以使用order by子句吗？