oracle之子查询

1 什么是子查询语法

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。

子查询语法

SELECT	select_list
FROM	table
WHERE	expr operator
		 	(SELECT	select_list
		        FROM		table);

子查询举例

SQL> SELECT last_name
  2  FROM   employees
  3  WHERE  salary >
  4                 (SELECT salary
  5                  FROM   employees
  6                  WHERE  last_name = 'Abel');

LAST_NAME
-------------------------
King
Kochhar
De Haan

注意事项
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询类型

2 单行子查询

• 只返回一行。
• 使用单行比较操作符。

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

执行单行子查询

需求：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

SQL> SELECT last_name, job_id, salary
  2  FROM   employees
  3  WHERE  job_id =
  4                  (SELECT job_id
  5                   FROM   employees
  6                   WHERE  employee_id = 141)
  7  AND    salary >
  8                  (SELECT salary
  9                   FROM   employees
 10                   WHERE  employee_id = 143);

LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Rajs                      ST_CLERK      3500.00
Davies                    ST_CLERK      3100.00
Nayer                     ST_CLERK      3200.00

在子查询中使用组函数

需求：返回公司工资最少的员工的last_name,job_id和salary

SQL> SELECT last_name, job_id, salary
  2  FROM   employees
  3  WHERE  salary =
  4                  (SELECT MIN(salary)
  5                   FROM   employees);

LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Olson                     ST_CLERK      2100.00

非法使用子查询

多行子查询使用单行比较符

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id)

ORA-01427: 单行子查询返回多个行
ERROR at line 4:
ORA-01427: single-row subquery returns more thanone row

子查询中的空值问题

no rows selected(子查询不返回任何行)

SQL> SELECT last_name, job_id
  2  FROM   employees
  3  WHERE  job_id =
  4                  (SELECT job_id
  5                   FROM   employees
  6                   WHERE  last_name = 'Haas');

LAST_NAME                 JOB_ID
------------------------- ----------

3 多行子查询

• 返回多行。
• 使用多行比较操作符。

操作符	含义
IN	等于列表中的任意一个
ANY	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

体会any和all的区别

在多行子查询中使用 ANY 操作符

返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM   employees
  3  WHERE  salary < ANY
  4                      (SELECT salary
  5                       FROM   employees
  6                       WHERE  job_id = 'IT_PROG')
  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        132 Olson                     ST_CLERK      2100.00
        128 Markle                    ST_CLERK      2200.00
        136 Philtanker                ST_CLERK      2200.00

在多行子查询中使用 ALL 操作符

返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
的员工号、姓名、job_id 以及salary

SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM   employees
  3  WHERE  salary < ALL
  4                      (SELECT salary
  5                       FROM   employees
  6                       WHERE  job_id = 'IT_PROG')
  7  AND    job_id <> 'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        185 Bull                      SH_CLERK      4100.00
        192 Bell                      SH_CLERK      4000.00
        193 Everett                   SH_CLERK      3900.00

子查询中的空值问题

SQL> SELECT emp.last_name
  2  FROM   employees emp
  3  WHERE  emp.employee_id NOT IN
  4                               (SELECT mgr.manager_id
  5                                FROM   employees mgr);

LAST_NAME
-------------------------
no rows selected

总 结

在查询时基于未知的值时，应使用子查询。