基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
子查询
更新时间：2023-07-18 17:25:58
分享
子查询是指嵌套在一个上层查询中的查询。SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。
子查询简介
子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 子句、FROM 子句、WHERE 子句等。
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，则外查询的结果为空。
子查询示例
SELECT 子句与标量子查询示例
obclient> SELECT dept.*, (SELECT COUNT(*) FROM emp WHERE emp.deptno = dept.deptno) AS emp_count FROM dept;
+--------+------------+-------------+-----------+
| deptno | dname | location | emp_count |
+--------+------------+-------------+-----------+
| 20 | ACCOUNTING | Los Angeles | 2 |
| 30 | OPERATIONS | CHICAGO | 2 |
| 40 | SALES | NEW YORK | 2 |
+--------+------------+-------------+-----------+
3 rows in set
obclient> SELECT (SELECT dept.dname FROM dept, emp WHERE dept.deptno = emp.deptno AND emp.empname = 'JONES') AS dname;
+-------+
| dname |
+-------+
| SALES |
+-------+
1 row in set
FROM 子句与表子查询示例
将子查询的结果集作为一张表，必须为其起别名，例如下面示例中的表别名为 t1。
obclient> SELECT t1.deptno, t1.avg_sal FROM (SELECT deptno, avg(emp.sal) AS avg_sal FROM emp GROUP BY emp.deptno) t1;
+--------+-------------+
| deptno | avg_sal |
+--------+-------------+
| 20 | 1900.000000 |
| 30 | 2225.000000 |
| 40 | 2987.500000 |
+--------+-------------+
3 rows in set
WHERE 子查询示例
查询比所有 MANAGER 工资都低的员工信息，示例如下：
obclient> SELECT empname, empno, job, sal FROM emp WHERE sal < ALL(SELECT sal FROM emp WHERE job = 'MANAGER');
+---------+-------+----------+---------+
| empname | empno | job | sal |
+---------+-------+----------+---------+
| SMITH | 1369 | CLERK | 800.00 |
| ALLEN | 1499 | SALESMAN | 1600.00 |
+---------+-------+----------+---------+
2 rows in set
查询员工号最大的员工信息，示例如下：
obclient> SELECT * FROM emp WHERE empno = (SELECT max(empno) 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
HAVING 子查询示例
查询工资最低的员工信息，示例如下：
obclient> SELECT empno, empname, sal FROM emp HAVING sal = (SELECT MIN(sal) FROM emp);
+-------+---------+--------+
| empno | empname | sal |
+-------+---------+--------+
| 1369 | SMITH | 800.00 |
+-------+---------+--------+
1 row in set
EXISTS 与相关子查询
EXISTS 子查询先执行主查询，得到主查询的结果，再根据子查询进行过滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。EXISTS 查询结果为 1 或 0，用来判断子查询的结果集中是否有值。一般来说，EXISTS 子查询都能用 IN 代替。
查询是否存在工资为 3000 的员工，示例如下：
obclient> SELECT EXISTS(SELECT * FROM emp WHERE sal = 3000) AS 'exist';
+-------+
| exist |
+-------+
| 1 |
+-------+
1 row in set
查询出所属部门不为空的员工信息，示例如下：
obclient> SELECT empno, empname, deptno FROM emp WHERE EXISTS(SELECT * FROM dept WHERE dept.deptno = emp.deptno);
+-------+---------+--------+
| empno | empname | deptno |
+-------+---------+--------+
| 1369 | SMITH | 20 |
| 1499 | ALLEN | 30 |
| 1566 | JONES | 40 |
| 1698 | BLAKE | 30 |
| 1788 | SCOTT | 20 |
| 1902 | FORD | 40 |
+-------+---------+--------+
6 rows in set
使用 IN 代替 EXISTS，示例如下：
obclient> SELECT empno, empname, deptno FROM emp WHERE deptno IN (SELECT deptno FROM dept);
+-------+---------+--------+
| empno | empname | deptno |
+-------+---------+--------+
| 1369 | SMITH | 20 |
| 1499 | ALLEN | 30 |
| 1566 | JONES | 40 |
| 1698 | BLAKE | 30 |
| 1788 | SCOTT | 20 |
| 1902 | FORD | 40 |
+-------+---------+--------+
6 rows in set
