子查询
📄字数 1.8K
👁️阅读量 加载中...
一、概述
子查询，也称为嵌套查询，允许出现在任何可以为表达式的地方。子查询可以是一个完整独立的查询语句，可以是包含选择列表的简单SELECT查询语句、包含一个或多个表或视图名称的FROM子句、可选的WHERE子句、可选的GROUP BY子句、可选的HAVING子句；子查询中亦可以嵌套子查询。
子查询可在查询选择列表中，如果子查询位于查询选择列表中，且作为独立字段，则只允许返回单行数据，否则数据库会抛出错误，若子查询位于WHERE条件后，则需配合比较谓词或其他谓词使用。
注意
子查询有以下限制：
- WHERE条件中若需要子查询返回多行则需要使用
EXISTS、IN、ANY、ALL等关键字修饰。 - 比较谓词、
BETWEEN、LIKE等关键字引入的子查询必须返回单行且单个字段。 - 如果多个子查询中的列与需要查询的列具有相同的名称，则必须对表的列的任何引用加上表名或别名。
二、标量子查询
2.1 功能描述
标量子查询的结果为单行单列，可作为返回字段，也可作为where子句的筛选条件等。
2.2 示例
sql
SQL> CREATE TABLE tab_sub_query(id INT, name VARCHAR(30), score INT);
Execute successful.
Use time:34 ms.
SQL> INSERT INTO tab_sub_query VALUES(1,'tudou',30)(2,'digua',40)(3,'qiezi',50)(4,'baicai',60);
Total 4 records effected.
Use time:2 ms.
SQL> SELECT id, name, (SELECT max(score) FROM tab_sub_query) AS max_score FROM tab_sub_query;
+----+--------+-----------+
| ID | NAME | MAX_SCORE |
+----+--------+-----------+
| 1 | tudou | 60 |
| 2 | digua | 60 |
| 3 | qiezi | 60 |
| 4 | baicai | 60 |
+----+--------+-----------+
(4 rows)
Use time:1 ms.三、列子查询
3.1 功能描述
列子查询的结果为单列多行，通常结合IN，ANY，SOME，ALL关键词使用。
IN：查询某字段的值是否存在于子查询的结果集中。ANY：通常结合<、>、=等比较运算符使用，子查询结果中的任意一个值满足比较条件即可。SOME：同ANY，通常结合<、>、=等比较运算符使用，子查询结果中的任意一个值满足比较条件即可。ALL：通常结合<、>、=等比较运算符使用，需要子查询结果中的所有值均满足条件。
3.2 示例
sql
SQL> SELECT id, name, score FROM tab_sub_query WHERE id IN(SELECT id FROM tab_sub_query WHERE score > 40);
+----+--------+-------+
| ID | NAME | SCORE |
+----+--------+-------+
| 3 | qiezi | 50 |
| 4 | baicai | 60 |
+----+--------+-------+
(2 rows)
Use time:0 ms.
SQL> SELECT id, name, score FROM tab_sub_query WHERE id > ANY(SELECT id FROM tab_sub_query WHERE score < 50);
+----+--------+-------+
| ID | NAME | SCORE |
+----+--------+-------+
| 2 | digua | 40 |
| 3 | qiezi | 50 |
| 4 | baicai | 60 |
+----+--------+-------+
(3 rows)
Use time:0 ms.
SQL> SELECT id, name, score FROM tab_sub_query WHERE id > SOME(SELECT id FROM tab_sub_query WHERE score < 50);
+----+--------+-------+
| ID | NAME | SCORE |
+----+--------+-------+
| 2 | digua | 40 |
| 3 | qiezi | 50 |
| 4 | baicai | 60 |
+----+--------+-------+
(3 rows)
Use time:0 ms.
SQL> SELECT id, name, score FROM tab_sub_query WHERE id > ALL(SELECT id FROM tab_sub_query WHERE score < 50);
+----+--------+-------+
| ID | NAME | SCORE |
+----+--------+-------+
| 3 | qiezi | 50 |
| 4 | baicai | 60 |
+----+--------+-------+
(2 rows)
Use time:0 ms.四、行子查询
4.1 功能描述
行子查询的结果为单行多列，可以配合元组比较，如(a, b) = (subquery)。
4.2 示例
sql
SQL> SELECT id, name, score FROM tab_sub_query WHERE (id, name) = (SELECT id, name FROM tab_sub_query WHERE score = 50);
+----+-------+-------+
| ID | NAME | SCORE |
+----+-------+-------+
| 3 | qiezi | 50 |
+----+-------+-------+
(1 row)
Use time:0 ms.五、表子查询
5.1 功能描述
表子查询的结果可以为多行多列，常用在FROM子句中，相当于一张临时表。
5.2 示例
sql
SQL> SELECT name, rnk FROM (SELECT id, name, RANK() OVER(ORDER BY score) AS rnk FROM tab_sub_query);
+--------+-----+
| NAME | RNK |
+--------+-----+
| tudou | 1 |
| digua | 2 |
| qiezi | 3 |
| baicai | 4 |
+--------+-----+
(4 rows)
Use time:0 ms.六、EXISTS关键词
6.1 功能描述
EXISTS无需指定条件，EXISTS后的子查询在无数据情况下返回false，存在数据情况下返回true，仅用作数据存在判断。
6.2 示例
sql
SQL> SELECT * FROM tab_sub_query WHERE EXISTS(SELECT id FROM tab_sub_query);
+----+--------+-------+
| ID | NAME | SCORE |
+----+--------+-------+
| 1 | tudou | 30 |
| 2 | digua | 40 |
| 3 | qiezi | 50 |
| 4 | baicai | 60 |
+----+--------+-------+
(4 rows)
Use time:2 ms.