Oracle之SQL的子查询使用及说明

前情提要：

本篇博客将详细介绍oracle中SQL的子查询，包括子查询相关的语法，使用规则，还有子查询的常见类型（单行子查询、多行子查询、多列子查询、内联视图），并且有详细的使用示例和解析

oracle版本：19c

一、子查询相关介绍

1.1 子查询的语法

1. 子查询（内部查询）在主查询（外部查询）之前执行。

2. 子查询的结果由主查询使用。

SELECTselect_listFROMtableWHEREexpr operator (SELECTselect_list       FROMtable);

1.2 使用子查询的规则和准则

1. 将子查询括在括号中（括号中的子查询语句不需要写分号）。

2. 虽然子查询可以出现在比较条件的任意一侧，但是一般将子查询放在比较条件的右侧，以提高可读性。

3. 将单行运算符用于单行子查询，将多行运算符用于多行子查询。

二、使用子查询解决查询问题

问题：谁在Davies之后入职?

-- 单独查询Davies的入职日期-- 先修改时间格式方便查看SQL> ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';Session altered.SQL> SELECT hire_date FROM employees WHERE last_name = 'Davies';HIRE_DATE----------2005-01-29-- 可见Davies的入职日期是2005-01-29，所以在Davies之后雇佣的员工的入职时间要比Davies的入职时间大（越接近现在的时间越大）SQL> SELECT LAST_NAME , HIRE_DATE  2  FROM EMPLOYEES  3  WHERE HIRE_DATE > (SELECT HIRE_DATE FROM EMPLOYEES WHERE LAST_NAME='Davies');  LAST_NAME                 HIRE_DATE------------------------- ----------Feeney                    2006-05-23OConnell                  2007-06-21Grant                     2008-01-13Fay                       2005-08-1781 rows selected.

2.1 在子查询中使用聚合函数

使用示例

1. 找到工资最低的人的信息

SQL> SELECT last_name, job_id, salary  2  FROM   employees  3  WHERE  salary = (SELECT MIN(salary) FROM employees); -- 子查询返回employees表中的最低工资LAST_NAME                 JOB_ID         SALARY------------------------- ---------- ----------Olson                     ST_CLERK         2100

2.2 子查询中的HAVING子句

1. Oracle服务器首先执行子查询。

2. Oracle服务器将结果返回到主查询的HAVING子句中。

使用示例

1. 找到哪些部门的最低工资是大于30号部门最低工资，并输出相关信息

SQL> SELECT   department_id, MIN(salary)  2  FROM     employees  3  GROUP BY department_id  4  HAVING   MIN(salary) >(SELECT MIN(salary) FROM employees WHERE  department_id = 30); -- 子查询找到了30号部门的最低工资DEPARTMENT_ID MIN(SALARY)------------- -----------           40        6500          110        8300           90       17000           70       10000                     7000           10        4400           20        6000           60        4200          100        6900           80        610010 rows selected.

三、子查询的类型

3.1 单行子查询

特点：

1. 只返回一行

2. 使用单行比较运算符（单行子查询返回单行单列的结果，使用单行比较运算符进行条件判断）

适用场景：

1. 查找比某个特定值（由子查询提供）更大的数据

2. 获取精确匹配的值

单行子查询执行示例

1. 找到和Austin一样的工作的人，并且这个（这些）人工资大于Austin

SQL> SELECT last_name, job_id, salary  2  FROM   employees  3  WHERE  job_id =  (SELECT job_id  4                   FROM   employees  5                   WHERE  last_name = 'Austin')-- 子查询查出Austin的工作ID  6  AND    salary >  (SELECT salary  7                   FROM   employees  8                   WHERE  last_name = 'Austin');-- 子查询查出Austin的工资LAST_NAME                 JOB_ID         SALARY------------------------- ---------- ----------Hunold                    IT_PROG          9000Ernst                     IT_PROG          6000-- 单独执行子查询确认结果是否准确SQL> SELECT job_id FROM employees WHERE last_name = 'Austin';JOB_ID----------IT_PROGSQL> SELECT salary FROM employees WHERE last_name = 'Austin';    SALARY----------      4800

3.2 多行子查询

特点：

1. 返回多行

2. 使用多行比较运算符

适用场景：

1. IN：匹配子查询返回列表中的任意一个值

2. ANY：满足子查询结果集中任意一行的条件

3. ALL：满足子查询结果集中所有行的条件

使用示例：

1. 在多行子查询中使用ANY运算符

-- 找到工资小于从事IT_PROG岗位的人的工资，并且不能包括IT_PROG人员。SQL> SELECT employee_id, last_name, job_id, salary  2  FROM   employees  3  WHERE  salary < ANY (SELECT salary FROM  employees WHERE  job_id = 'IT_PROG')-- 子查询返回IT_PROG工作的所有工资  4  AND    job_id <> 'IT_PROG';  EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY----------- ------------------------- ---------- ----------        122 Kaufling                  ST_MAN           7900        153 Olsen                     SA_REP           8000        159 Smith                     SA_REP           8000        120 Weiss                     ST_MAN           8000        121 Fripp                     ST_MAN           8200        110 Chen                      FI_ACCOUNT       8200        206 Gietz                     AC_ACCOUNT       8300        177 Livingston                SA_REP           8400        176 Taylor                    SA_REP           8600        175 Hutton                    SA_REP           880076 rows selected.-- 单独查看子查询的结果SQL> SELECT salary FROM  employees WHERE  job_id = 'IT_PROG';    SALARY----------      9000      6000      4800      4800      4200      -- 所以上述示例可以理解为找出只要工作不是IT_PROG并且工资小于9000的所有人的信息

1. 在多行子查询中使用ALL运算符

SQL> SELECT employee_id, last_name, job_id, salary  2  FROM   employees  3  WHERE  salary < ALL(SELECT salary FROM  employees WHERE  job_id = 'IT_PROG')  4  AND    job_id <> 'IT_PROG';EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY----------- ------------------------- ---------- ----------        140 Patel                     ST_CLERK         2500        131 Marlow                    ST_CLERK         2500        119 Colmenares                PU_CLERK         2500        191 Perkins                   SH_CLERK         2500        182 Sullivan                  SH_CLERK         2500        144 Vargas                    ST_CLERK         2500        127 Landry                    ST_CLERK         2400        135 Gee                       ST_CLERK         2400        128 Markle                    ST_CLERK         2200        136 Philtanker                ST_CLERK         2200        132 Olson                     ST_CLERK         210044 rows selected.-- 该例可以理解为找出只要工作不是IT_PROG并且工资小于IT_PROG岗的最低工资的所有人的信息

3.3 多列子查询

特点：

1. 多列子查询将多个列返回到外部查询。

2. 多列比较中的列比较可以成对或非成对。

3. 也可以在SELECT语句的FROM子句中使用多列子查询。

4. 可以是单行多列或多行多列

适用场景：

1. 需要同时匹配多个条件的复杂查询

2. 比较多个列的值

使用示例

1. 显示每个部门中薪水最低的员工

SQL> SELECT first_name, department_id, salary  2  FROM employees  3  WHERE (salary, department_id) IN (SELECT MIN(salary), department_id FROM employees GROUP BY department_id)  4  ORDER BY department_id;FIRST_NAME           DEPARTMENT_ID     SALARY-------------------- ------------- ----------Jennifer                        10       4400Pat                             20       6000Karen                           30       2500Susan                           40       6500TJ                              50       2100Diana                           60       4200Hermann                         70      10000Sundita                         80       6100Neena                           90      17000Lex                             90      17000Luis                           100       6900FIRST_NAME           DEPARTMENT_ID     SALARY-------------------- ------------- ----------William                        110       830012 rows selected.-- 子查询返回的是多行多列数据，在该例中进行多列匹配，当工资和部门ID同时匹配子查询的结果时条件成立。-- 单独查看子查询结果SQL> SELECT MIN(salary), department_id FROM employees GROUP BY department_id;MIN(SALARY) DEPARTMENT_ID----------- -------------       2100            50       6500            40       8300           110      17000            90       2500            30      10000            70       7000       4400            10       6000            20       4200            60       6900           100MIN(SALARY) DEPARTMENT_ID----------- -------------       6100            8012 rows selected.

3.4 内联视图

特点：

1. 在FROM子句中使用的子查询，作为派生表提供临时结果集

2. 必须为内联视图指定别名

3. 外层查询引用其列时需采用"别名.列名"格式

4. 可以包含ORDER BY子句（与普通子查询不同）

适用场景

1. 将聚合结果作为虚拟表进行关联

2. 创建临时结果集供主查询使用

3. 实现复杂的数据转换和计算

使用示例：

-- 查看所有员工总工资SQL> SELECT SUMS FROM (SELECT SUM(SALARY) AS SUMS  FROM EMPLOYEES);      SUMS----------    691416    -- 虽然该例等价于SELECT SUM(SALARY) AS SUMS FROM EMPLOYEES，但是此处主要为了体现内联视图的作用才这么写。

四、子查询错误使用示例

示例

1. 使用单行运算符匹配多行子查询返回的结果

SQL> SELECT employee_id, last_name  2  FROM   employees  3  WHERE  salary = (SELECT   MIN(salary) FROM employees GROUP BY department_id);WHERE  salary = (SELECT   MIN(salary) FROM employees GROUP BY department_id)                 *ERROR at line 3:ORA-01427: single-row subquery returns more than one row-- 错误原因：使用单行运算符匹配多行子查询返回的结果-- 单独查看子查询SQL> SELECT   MIN(salary) FROM employees GROUP BY department_id;-- 查找每个部门的最低工资MIN(SALARY)-----------       2100       6500       8300      17000       2500      10000       7000       4400       6000       4200       6900MIN(SALARY)-----------       610012 rows selected.

1. 子查询不返回任何行

SQL> SELECT last_name, job_id  2  FROM   employees  3  WHERE  job_id = (SELECT job_id FROM   employees WHERE  last_name = 'Haas');no rows selected-- 这个语句本身没有语法错误，但是员工表中没有Haas这个人的信息，所以子查询返回的是空值-- 查看子查询SQL> SELECT job_id FROM   employees WHERE  last_name = 'Haas';no rows selected

总结