暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / OceanBase Cloud快速入门第66期：如何使用子查询?

OceanBase Cloud快速入门第66期：如何使用子查询?

分布式数据库爱好者 2023-04-20
138

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/


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

子查询简介

子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 子句、FROM 子句、WHERE 子句等。SELECT 语句的 FROM 子句中的子查询也称为内联视图。您可以在内联视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。

子查询类别

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

子查询类别结果集相关子句
标量子查询单列单行
  • SELECT 子句
  • WHERE 子句
  • HAVING 子句
列子查询单列多行
  • WHERE 子句
  • HAVING 子句
行子查询多列多行
  • WHERE 子句
  • HAVING 子句
表子查询多行多列
  • FROM 子句
  • EXISTS 子句

子查询应用场景

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

  • 在 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 和 dept，并插入适当数据。

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');

CREATE TABLE emp(  
    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),   
    CONSTRAINT PK_emp PRIMARY KEY (empno), 
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);

CREATE TABLE job_grades
(
     grade_level VARCHAR(5),
     lowest_sal INT,
     highest_sal INT);

INSERT INTO job_grades VALUE ('A', 1000, 1999);
INSERT INTO job_grades VALUE ('B', 2000, 2999);
INSERT INTO job_grades VALUE ('C', 3000, 3999);
INSERT INTO job_grades VALUE ('D', 40000, 4999);
INSERT INTO job_grades VALUE ('E', 5000, 5999);

SELECT 子句与标量子查询示例

obclient> SELECT a.*,(SELECT count(*) FROM emp b WHERE b.deptno = a.deptno) AS 员工数量 FROM dept a;
+--------+------------+-------------+--------------+
| deptno | dname      | location    | 员工数量      |
+--------+------------+-------------+--------------+
|     20 | ACCOUNTING | Los Angeles |            2 |
|     30 | OPERATIONS | CHICAGO     |            2 |
|     40 | SALES      | NEW YORK    |            2 |
+--------+------------+-------------+--------------+
3 rows in set

obclient> SELECT (SELECT a.dname FROM dept a, emp b WHERE a.deptno = b.deptno AND b.empno = 1566) AS 部门名称;
+--------------+
| 部门名称      |
+--------------+
| SALES        |
+--------------+
1 row in set

FROM 子句与表子查询示例

将子查询的结果集作为一张表，必须为其起别名，例如下面示例中的表别名为 t1 和 t2。

obclient> SELECT t1.deptno,sa AS '平均工资', t2.grade_level FROM (SELECT deptno,avg(a.sal) sa FROM emp a GROUP BY a.deptno) t1, job_grades t2 WHERE t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+--------+--------------+-------------+
| deptno | 平均工资     | grade_level |
+--------+--------------+-------------+
|     20 |  1900.000000 | A           |
|     30 |  2225.000000 | B           |
|     40 |  2987.500000 | B           |
+--------+--------------+-------------+
3 rows in set

带有 WHERE 和 HAVING 的子查询示例

  • 标量子查询

    /*查询最低工资大于 30 号部门最低工资的部门编号和其最低工资*/
obclient> SELECT min(a.sal) minsalary,deptno FROM emp a GROUP BY a.deptno HAVING min(a.sal) > (SELECT min(sal) FROM emp WHERE deptno = 30);
+-----------+--------+
| minsalary | deptno |
+-----------+--------+
|   2975.00 |     40 |
+-----------+--------+
1 row in set

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

    /*返回其他职位中比 job 为 MANAGER 所有工资低的员工的员工编号、员工姓名、职位和薪水*/

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

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

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

    obclient> SELECT * FROM emp a WHERE a.empno = (SELECT max(empno) FROM emp) AND sal = (SELECT max(sal) FROM emp);
+-------+---------+---------+------+------------+---------+------+--------+
| empno | empname | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+---------+---------+------+------------+---------+------+--------+
|  1902 | FORD    | ANALYST | 1566 | 1981-12-05 | 3000.00 | NULL |     40 |
+-------+---------+---------+------+------------+---------+------+--------+
1 row in set

EXISTS 与相关子查询

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

示例如下：

obclient> SELECT exists(SELECT empno FROM emp WHERE sal = 3000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0      |
+----------------------+
|                    1 |
+----------------------+
1 row in set

obclient> SELECT dname FROM dept a WHERE exists(SELECT 1 FROM emp b WHERE a.deptno = b.deptno);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set

obclient> SELECT dname FROM dept a WHERE a.deptno IN (SELECT deptno FROM emp);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set


欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/

oceanbase
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论