子查询

子查询是指嵌套在一个上层查询中的查询。SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

子查询简介

子查询指的是 SELECT 查询语句中嵌套了另一个或者多个 SELECT 语句，可以返回单行结果、多行结果或不返回结果。SELECT 语句的 FROM 子句中的子查询也称为内联视图，SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。

子查询类别

根据结果集的行列数不同，可以分为如下四类子查询：

子查询应用场景

子查询主要用于以下场景：

• 在 INSERT 或 CREATE TABLE 语句中定义要插入到的目标表中的行集。

• 在 CREATE VIEW 语句中定义要包含在视图中的行集。

• 在 UPDATE 语句中定义要分配给现有行的一个或多个值。

• 为 SELECT、UPDATE 和 DELETE 语句的 WHERE 子句、HAVING 子句或 START WITH 子句中的条件提供值。

子查询关键词

子查询中的关键词包括 IN、ANY、SOME 和 ALL 等。

• IN 常用于 WHERE 表达式中，用于查询某个范围内的数据。

• ANY 和 SOME 可以与 =、>、>=、<、<=、<> 操作符结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

• ALL 可以与 =、>、>=、<、<=、<> 操作符结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

  注意

  使用 NOT IN 时，如果子查询中列的值为 NULL，外查询的结果为空。

示例

创建示例表 emp_ny、 dept_ny、job_grades_ny，并插入数据。

1. 创建 dept_ny 表。

   obclient> CREATE TABLE dept_ny(
              deptno           NUMBER(2,0),
              dname            VARCHAR(14),
              location         VARCHAR(13),
              CONSTRAINT pk_dept PRIMARY KEY(deptno)
             );
   Query OK, 0 rows affected
   

2. 创建 job_grades_ny 表。

   obclient> CREATE TABLE job_grades_ny
             (
             grade_level VARCHAR(5),
             lowest_sal NUMBER,
             highest_sal NUMBER);
   Query OK, 0 rows affected
   

3. 创建 emp_ny 表。

   obclient> CREATE TABLE emp_ny(
              empno         NUMBER(4,0),
              empname       VARCHAR(10),
              job           VARCHAR(9),
              mgr           NUMBER(4,0),
              hiredate      DATE,
              sal           NUMBER(7,2),
              comm          NUMBER(7,2),
              deptno        NUMBER(2,0),
              age           NUMBER(2,0),
              CONSTRAINT PK_emp PRIMARY KEY (empno),
              CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept_ny (deptno)
              );
   Query OK, 0 rows affected
   

4. 插入数据到 dept_ny 表。

   obclient> INSERT INTO dept_ny VALUES
             (20,'Finance','beijing'),
             (35,'Administration','hangzhou'),
             (40,'Development','xian'),
             (30,'Workshop','guangzhou'),
             (25,'Legal affairs','shanghai'),
             (45,'Office','suzhou');
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

5. 插入数据到 job_grades_ny 表。

   obclient> INSERT INTO job_grades_ny VALUES ('A', 1000, 1999),
             ('B', 2000, 2999),('C', 3000, 3999),('D', 40000, 4999),
             ('E', 5000, 5999);
   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

6. 插入数据到 emp_ny 表。

   obclient> INSERT INTO emp_ny VALUES
               (1369,'SMITH','CLERK',1902,DATE'1980-12-17',800.00,NULL,20,22),
               (1499,'ALLEN','SALESMAN',1698,DATE'1981-02-20',1600.00,300.00,35,22),
               (1566,'JONES','MANAGER',1839,DATE'1981-04-02',2975.00, NULL,40,22),
               (1698,'BLAKE' ,'MANAGER',1839,DATE'1981-05-01',2850.00,NULL ,30,33),
               (1788,'SCOTT','ANALYST',1566,DATE'1987-07-15',3000.00,NULL ,25,33),
               (1902,'FORD','ANALYST',1566,DATE'1981-12-05',3000.00, NULL,45,22);
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

示例一：SELECT 子句与标量子查询

• 通过标量子查询查询出各部门的员工数量。

  obclient> SELECT a.*,(SELECT count(*) FROM emp_ny b WHERE b.deptno = a.deptno) AS 员工数量 FROM dept_ny a;
  +--------+----------------+-----------+--------------+
  | DEPTNO | DNAME          | LOCATION  | 员工数量     |
  +--------+----------------+-----------+--------------+
  |     20 | Finance        | beijing   |            1 |
  |     25 | Legal affairs  | shanghai  |            1 |
  |     30 | Workshop       | guangzhou |            1 |
  |     35 | Administration | hangzhou  |            1 |
  |     40 | Development    | xian      |            1 |
  |     45 | Office         | suzhou    |            1 |
  +--------+----------------+-----------+--------------+
  6 rows in set
  

• 通过标量子查询查询出 empno =1566 对应的部门。

  obclient> SELECT (SELECT a.dname FROM dept_ny a, emp_ny b WHERE a.deptno = b.deptno AND b.empno = 1566) AS 部门名称 FROM emp_ny b;
  +--------------+
  | 部门名称     |
  +--------------+
  | Development  |
  | Development  |
  | Development  |
  | Development  |
  | Development  |
  | Development  |
  +--------------+
  6 rows in set
  

示例二：带有 WHERE 和 HAVING 的子查询

• 标量子查询

  通过 WHERE 和 HAVING 的子查询查询出最低工资大于 30 号部门最低工资的部门编号和其最低工资。

  obclient> SELECT min(a.sal) minsalary,deptno FROM emp_ny a GROUP BY a.deptno
           HAVING min(a.sal) > (SELECT min(sal) FROM emp_ny WHERE deptno = 30);
  +-----------+--------+
  | MINSALARY | DEPTNO |
  +-----------+--------+
  |      3000 |     25 |
  |      2975 |     40 |
  |      3000 |     45 |
  +-----------+--------+
  3 rows in set
  

• 列子查询返回一列多行的结果集

  通过列子查询返回其他职位中比 job 为 MANAGER 所有工资低的员工的员工编号、员工姓名、职位和薪水。

  obclient> SELECT empname 员工姓名, empno 员工编号, job 职位, sal 薪水 FROM emp_ny 
           WHERE sal < ALL
           (SELECT DISTINCT sal FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+--------+
  | 员工姓名     | 员工编号     | 职位     | 薪水   |
  +--------------+--------------+----------+--------+
  | SMITH        |         1369 | CLERK    |    800 |
  | ALLEN        |         1499 | SALESMAN |   1600 |
  +--------------+--------------+----------+--------+
  2 rows in set
  

  通过列子查询 + 聚合函数 min 返回其他职位中比 job 为 MANAGER 所有工资低的员工的员工编号、员工姓名、职位和薪水。

  obclient> SELECT empname 员工姓名, empno 员工编号, job 职位, sal 薪水 FROM emp_ny
           WHERE sal < ALL (SELECT min(sal) FROM emp_ny WHERE job = 'MANAGER') AND job!= 'MANAGER';
  +--------------+--------------+----------+--------+
  | 员工姓名      | 员工编号     | 职位      | 薪水   |
  +--------------+--------------+----------+--------+
  | SMITH        |         1369 | CLERK    |    800 |
  | ALLEN        |         1499 | SALESMAN |   1600 |
  +--------------+--------------+----------+--------+
  2 rows in set
  

• 行子查询返回一行多列的结果集

  通过行子查询查询出 empno 最大与薪水最高的员工。

  obclient> SELECT * FROM emp_ny a 
           WHERE a.empno = (SELECT max(empno) FROM emp_ny) 
           AND sal = (SELECT max(sal) FROM emp_ny);
  +-------+---------+---------+------+-----------+------+------+--------+------+
  | EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  |  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
  +-------+---------+---------+------+-----------+------+------+--------+------+
  1 row in set
  

示例三：EXISTS 与相关子查询

EXISTS 子查询先执行主查询，得到主查询的结果，再根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。EXISTS 查询结果为 1 或 0，用来判断子查询的结果集中是否有值。一般来说，EXISTS 子查询都能用 IN 代替。

• 通过 EXISTS 子句查询出 dept_ny 表与 emp_ny 编号 deptno 相同的 DNAME 数据。

  obclient> SELECT dname FROM dept_ny a WHERE EXISTS(SELECT 1 FROM emp_ny b WHERE a.deptno = b.deptno);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

• 通过 IN 查询出 dept_ny 表与 emp_ny 编号 deptno 相同的 DNAME 数据。

  obclient> SELECT dname FROM dept_ny a WHERE a.deptno IN (SELECT deptno FROM emp_ny);
  +----------------+
  | DNAME          |
  +----------------+
  | Finance        |
  | Legal affairs  |
  | Workshop       |
  | Administration |
  | Development    |
  | Office         |
  +----------------+
  6 rows in set
  

示例四：MULTISET(subquery) 子查询

CAST(MULTISET (subquery)) AS type_name 的结果是集合类型，子查询产生的行被转换为对应的集合元素。示例如下：

obclient [USER001]> CREATE TYPE cust_tbl2 AS TABLE OF varchar2(20);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl1(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> CREATE TABLE tbl2(col1 INT, col2 INT);
Query OK, 0 rows affected

obclient [USER001]> INSERT INTO tbl1 VALUES (1,1);
Query OK, 1 row affected

obclient [USER001]> INSERT INTO tbl2 VALUES (1,2),(1,3);
Query OK, 2 rows affected
 Records: 2  Duplicates: 0  Warnings: 0

obclient [USER001]> SELECT * FROM tbl1,TABLE(CAST(MULTISET(SELECT tbl2.col2 FROM tbl2 WHERE tbl1.col1=tbl2.col1) AS cust_tbl2));
+------+------+--------------+
| COL1 | COL2 | COLUMN_VALUE |
+------+------+--------------+
|    1 |    1 | 2            |
|    1 |    1 | 3            |
+------+------+--------------+
2 rows in set

相关文档

• SELECT
• CAST
• CREATE TYPE