# 子查询

## 子查询语句

• 标量子查询（Scalar Subquery），如 `SELECT (SELECT s1 FROM t2) FROM t1`
• 派生表（Derived Tables），如 `SELECT t1.s1 FROM (SELECT s1 FROM t2) t1`
• 存在性测试（Existential Test），如 `WHERE NOT EXISTS(SELECT ... FROM t2)``WHERE t1.a IN (SELECT ... FROM t2)`
• 集合比较（Quantified Comparison），如 `WHERE t1.a = ANY(SELECT ... FROM t2)`
• 作为比较运算符操作数的子查询，如 `WHERE t1.a > (SELECT ... FROM t2)`

## 子查询的分类

### 无关联子查询

``````.css-1qhimia{margin-right:3rem;overflow:auto;}SELECT * FROM authors a1 WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > (
SELECT
AVG(IFNULL(a2.death_year, YEAR(NOW())) - a2.birth_year) AS average_age
FROM
authors a2
)
.css-ux9q70{text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:2.4rem;background:transparent;border:unset;}.css-ux9q70:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-ux9q70:hover{background-color:transparent;}}.css-ux9q70.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-1rmx1rm{display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;-webkit-box-pack:center;-ms-flex-pack:center;-webkit-justify-content:center;justify-content:center;position:relative;box-sizing:border-box;-webkit-tap-highlight-color:transparent;background-color:transparent;outline:0;border:0;margin:0;border-radius:0;padding:0;cursor:pointer;-webkit-user-select:none;-moz-user-select:none;-ms-user-select:none;user-select:none;vertical-align:middle;-moz-appearance:none;-webkit-appearance:none;-webkit-text-decoration:none;text-decoration:none;color:inherit;text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:2.4rem;background:transparent;border:unset;}.css-1rmx1rm::-moz-focus-inner{border-style:none;}.css-1rmx1rm.Mui-disabled{pointer-events:none;cursor:default;}@media print{.css-1rmx1rm{-webkit-print-color-adjust:exact;color-adjust:exact;}}.css-1rmx1rm:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-1rmx1rm:hover{background-color:transparent;}}.css-1rmx1rm.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-1e2dcm1{z-index:1500;pointer-events:none;}.css-okvapm{z-index:1500;pointer-events:none;}.css-rnn29l{text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:0.625rem;background:transparent;border:unset;}.css-rnn29l:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-rnn29l:hover{background-color:transparent;}}.css-rnn29l.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-fxo539{display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;-webkit-box-pack:center;-ms-flex-pack:center;-webkit-justify-content:center;justify-content:center;position:relative;box-sizing:border-box;-webkit-tap-highlight-color:transparent;background-color:transparent;outline:0;border:0;margin:0;border-radius:0;padding:0;cursor:pointer;-webkit-user-select:none;-moz-user-select:none;-ms-user-select:none;user-select:none;vertical-align:middle;-moz-appearance:none;-webkit-appearance:none;-webkit-text-decoration:none;text-decoration:none;color:inherit;text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:0.625rem;background:transparent;border:unset;}.css-fxo539::-moz-focus-inner{border-style:none;}.css-fxo539.Mui-disabled{pointer-events:none;cursor:default;}@media print{.css-fxo539{-webkit-print-color-adjust:exact;color-adjust:exact;}}.css-fxo539:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-fxo539:hover{background-color:transparent;}}.css-fxo539.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}``````

``````SELECT AVG(IFNULL(a2.death_year, YEAR(NOW())) - a2.birth_year) AS average_age FROM authors a2;
``````

``````SELECT * FROM authors a1
WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > 34;
``````

``````+--------+-------------------+--------+------------+------------+
| id     | name              | gender | birth_year | death_year |
+--------+-------------------+--------+------------+------------+
| 13514  | Kennith Kautzer   | 1      | 1956       | 2018       |
| 13748  | Dillon Langosh    | 1      | 1985       | NULL       |
| 99184  | Giovanny Emmerich | 1      | 1954       | 2012       |
| 180191 | Myrtie Robel      | 1      | 1958       | 2009       |
| 200969 | Iva Renner        | 0      | 1977       | NULL       |
| 209671 | Abraham Ortiz     | 0      | 1943       | 2016       |
| 229908 | Wellington Wiza   | 1      | 1932       | 1969       |
| 306642 | Markus Crona      | 0      | 1969       | NULL       |
| 317018 | Ellis McCullough  | 0      | 1969       | 2014       |
| 322369 | Mozelle Hand      | 0      | 1942       | 1977       |
| 325946 | Elta Flatley      | 0      | 1933       | 1986       |
| 361692 | Otho Langosh      | 1      | 1931       | 1997       |
| 421294 | Karelle VonRueden | 0      | 1977       | NULL       |
...
``````

## 关联子查询

``````SELECT * FROM authors a1 WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > (
SELECT
AVG(
IFNULL(a2.death_year, YEAR(NOW())) - IFNULL(a2.birth_year, YEAR(NOW()))
) AS average_age
FROM
authors a2
WHERE a1.gender = a2.gender
);
``````

TiDB 在处理该 SQL 语句是会将其改写为等价的 Join 查询：

``````SELECT *
FROM
authors a1,
(
SELECT
gender, AVG(
IFNULL(a2.death_year, YEAR(NOW())) - IFNULL(a2.birth_year, YEAR(NOW()))
) AS average_age
FROM
authors a2
GROUP BY gender
) a2
WHERE
a1.gender = a2.gender
AND (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > a2.average_age;
``````

## 扩展阅读

TiDB
TiDB Cloud
© 2023 PingCAP. All Rights Reserved.