（10） 子查询
最后更新于：2022-04-01 23:40:02
**目录**
[TOC]
# 1 一个叙述中的查询叙述
子查询(subquery)是一种很常见的应用，不论是查询、新增、修改或删除都有可能出现。子查询是一个放在左右刮号中的“SELECT”叙述，而这个查询叙述会放在另一个SQL叙述中。在执行一些工作的时候，使用子查询可以简化SQL叙述。以查询“人口比美国多的国家”来说，你要先执行下列查询美国人口数量的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eaffa8a17.png)
知道美国人口数量以后，再执行下列的叙述就可以传回人口比美国多的国家了：
[](http://box.kancloud.cn/2015-09-15_55f7eb003beda.png)
以这样的查询来说，你要执行两次查询叙述来完成这个工作。不过遇到类似这样的需求时：
[](http://box.kancloud.cn/2015-09-15_55f7eb008ee9b.png)
你就可以考虑把它们写成一个叙述就可以了：
[](http://box.kancloud.cn/2015-09-15_55f7eb00f25cb.png)
上列的范例是一种很常见的子查询应用，使用子查询的好处是不用执行多次查询就可以完成工作，这样可以简化查询的工作；对于处理资料的应用程式来说，也可以节省一些程式码。
# 2 WHERE、HAVING子句与子查询
子查询大部份使用在提供判断条件用的资料，在“WHERE”和“HAVING”子句中，都可能出现子查询：
[](http://box.kancloud.cn/2015-09-15_55f7eb01b3c00.png)
## 2.1 比较运算子
在“WHERE”和“HAVING”子句中，你会使用许多不同的运算子来判断条件是否符合。这些运算子中的比较运算子都可以搭配子查询来完成你的需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb020d9ba.png)
使用比较运算子的时候，你要提供一个资料让运算子判断条件是否符合。在使用子查询提供判断用的资料时，要特别注意子查询回传的资料是否符合规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb07681f5.png)
以下列“查询GNP最大的国家”需求来说，子查询传回的数字是“country”表格中“GNP”字段的最大值，这个数字就给外层查询当作“WHERE”子句中的条件设定：
[](http://box.kancloud.cn/2015-09-15_55f7eb07c5b9d.png)
使用在比较运算子的子查询，在“SELECT”子句中不可以指定超过一个字段的回传资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb0843997.png)
子查询也不可以回传超过一笔以上的纪录：
[](http://box.kancloud.cn/2015-09-15_55f7eb087d11b.png)
## 2.2 “IN”运算子
除了一般的比较运算子外，你可能很常使用“IN”运算子来执行多个资料的比较，你也可以使用子查询提供“IN”运算子判断的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb08c0e34.png)
如果你想要查询“城市人口超过九百万的国家”，“IN”运算子就会出现在这类的需求中：
[](http://box.kancloud.cn/2015-09-15_55f7eb0929e97.png)
这类的需求，也可以改成使用子查询来完成：
[](http://box.kancloud.cn/2015-09-15_55f7eb0eae3be.png)
以上列的范例来说，如果你用错运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb0f2773a.png)
“IN”运算子可以视需要搭配“NOT”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb0f8a2ba.png)
## 2.3 其它运算子
比较运算子与子查询搭配使用时，另外还提供“ALL”、“ANY”与“SOME”三个运算子，其中“ANY”和“SOME”运算子的效果是一样的，所以只需要讨论“ALL”与“ANY”这两个运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb105b94b.png)
比较运算子与“ALL”与“ANY”搭配使用时，可以完成比较特殊的查询需求，下列是两个用来测试的表格：
[](http://box.kancloud.cn/2015-09-15_55f7eb10ce012.png)
下列是比较运算子与“ALL”搭配使用的范例：
[](http://box.kancloud.cn/2015-09-15_55f7eb11570e8.png)
“ALL”运算子从字面上来看，是“全部”的意思，所以你也可以这样来看“ALL”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb11d5887.png)
“ANY”运算子从字面上来看，是“任何一个”的意思，所以你也可以这样来看“ANY”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb122aa6c.png)
注：在MySQL中，“ANY”与“SOME”运算子的效果是一样的。
在你了解“ALL”运算子的效果以后，如果在解决你的需求时，使用了“ ALL”这样的运算子，它的效果其实跟“NOT IN”是一样的：
[](http://box.kancloud.cn/2015-09-15_55f7eb12b144a.png)
另外“= ANY”运算子的效果跟“IN”是一样的：
[](http://box.kancloud.cn/2015-09-15_55f7eb1335111.png)
## 2.4 多字段子查询
在条件设定的时候，通常会也遇到比较复杂一点的设定，例如下列这个查询“在亚洲而且政府型式为Republic的国家”叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb13732f9.png)
上列的条件设定，有另外一种比较简单的设定方式：
[](http://box.kancloud.cn/2015-09-15_55f7eb1406c1b.png)
如果想要查询“跟Iraq国家同一个地区，而且跟Iraq国家的政府型式一样的国家”，因为判断条件都要经由查询才可以得到，所以你可能会写出这样的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb1474a92.png)
遇到类似这样的需求时，你也可以套用这种比较简单的设定方式：
[](http://box.kancloud.cn/2015-09-15_55f7eb14a89fe.png)
如果想要查询“每一洲GNP最高的国家”，你可以使用下列的叙述先查询每一洲最高的GNP：
[](http://box.kancloud.cn/2015-09-15_55f7eb1556271.png)
跟单一资料的判断一样，子查询传回多比纪录时就要使用“IN”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb1594241.png)
# 3 SELECT子句与子查询
如果需要的话，子查询也可以使用在“SELECT”子句中。以查询“国家Japan的GNP”的需求来说，下列的范例使用的是你已经熟悉的查询叙述来完成这个需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb163696f.png)
这类的需求也可以直接在“SELECT”子句中使用子查询传回你需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb1baadc7.png)
下列的叙述可以查询“India国家占全世界人口的比例”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1bda0f8.png)
# 4 FROM子句与子查询
子查询可以使用在“WHERE”与“HAVGIN”子句中用来设定条件，还有使用在“SELECT”子句中，用来传回需要的资料；除了这两种用法外，子查询还可以使用在“FROM”子句。你通常会在在查询叙述的“FROM”子句中，指定需要的表格名称，有需要的话，你也可以使用子查询，这个子查询回传的结果会被当成一个“表格”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1e2f02a.png)
下列的范例可以查询“亚洲GNP前十名国家”：
[](http://box.kancloud.cn/2015-09-15_55f7eb1e72e42.png)
注：要完成上列的需求，并不需要在“FROM”子句中使用子查询，只要使用一般的查询叙述就可以了。
如果以查询“国家的官方语言与人口比例”的需求来说，你可以使用下列的查询叙述来完成这个工作：
[](http://box.kancloud.cn/2015-09-15_55f7eb23c84a6.png)
注：要完成上列的需求，并不需要在“FROM”子句中使用子查询，使用结合查询也可以得到一样的结果。
# 5 资料维护与子查询
在使用“INSERT”、“UPDATE”与“DELETE”叙述执行新增、修改与删除资料时，也可以依照需要使用子查询来简化资料维护的叙述。
## 5.1 新增
一般来说，使用“INSERT”叙述执行新增纪录的工作时，通常是直接指定新增纪录的资料；如果你要新增的资料，可以执行一个查询来取得的话，就可以搭配子查询来简化新增纪录的工作：
[](http://box.kancloud.cn/2015-09-15_55f7eb29cf80b.png)
以下列这个储存国家资料的表格(world.mycountry)来说：
[](http://box.kancloud.cn/2015-09-15_55f7eb2a62fe3.png)
如果你想要新增亚洲国家的资料到“mycountry”表格中，你可以使用子查询传回新增纪录需要的资料给“INSERT”叙述使用：
[](http://box.kancloud.cn/2015-09-15_55f7eb2ae4a0c.png)
使用子查询提供“INSERT”叙述需要的资料，要特别注意子查询回传的字段资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb30640a4.png)
注：搭配“ON DUPLICATE KEY UPDATE”的效果在“资料维护、新增、索引值与ON DUPLICATE KEY UPDATE”中讨论。
MySQL另外一种新增纪录的“REPLACE”叙述，也可以使用子查询提供需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42749af.png)
## 5.2 修改
使用“UPDATE”叙述执行修改资料时，如果没有使用“WHERE”子句指定修改的条件，“UPDATE”叙述会修改表格中所有的纪录；所以执行修改纪录资料的时候，通常会使用“WHERE”子句指定修改的条件。在“UPDATE”叙述的“WHERE”子句中，也可以使用子查询提供判断条件的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要执行“SALES部门的员工加薪百分之五”，因为你需要先知道“SALES”部门的编号，所以你可以使用子查询传回“SALES”部门的编号，给“UPDATE”叙述中的“WHERE”子句设定部门编号的条件：
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“UPDATE”叙述中的子查询有一个特别的规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb437f635.png)
## 5.3 删除
使用“DELETE”叙述执行删除纪录时，如果没有使用“WHERE”子句指定删除的条件，“DELETE”叙述会删除表格中所有的纪录；所以执行删除纪录的时候，通常会使用“WHERE”子句指定删除的条件。在“DELETE”叙述的“WHERE”子句中，也可以使用子查询提供判断条件的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb42e20f0.png)
如果要执行“删除SALES部门员工”，因为你需要先知道“SALES”部门的编号，所以你可以使用子查询传回“SALES”部门的编号，给“DELETE”叙述中的“WHERE”子句设定部门编号的条件：
[](http://box.kancloud.cn/2015-09-15_55f7eb434b1f4.png)
MySQL在“DELETE”叙述中出现的子查询有一个特别的规定：
[](http://box.kancloud.cn/2015-09-15_55f7eb4e7dbfd.png)
# 6 关联子查询
在查询或维护的查询中，都有可能会使用子查询来提供执行叙述所需要的资料：
[](http://box.kancloud.cn/2015-09-15_55f7eb4ec20a9.png)
在使用子查询的的时候，通常不会跟外层查询有直接的关系，也就是子查询不会使用外层查询的资料；不过遇到一些比较特殊的需求时，在“WHERE”或“HAVING”子句中的子查询，也需要使用外层查询的资料来执行判断的工作，这样的叙述称为“关联子查询、correlated subqueries”：
[](http://box.kancloud.cn/2015-09-15_55f7eb54281d1.png)
在“WHERE”或“HAVING”子句中用来设定条件的子查询，可以依照需求使用像“IN”、“ANY”这些运算子来判断条件是否符合。除了上列以经讨论的比较运算子外，还有一个“EXISTS”运算子：
[](http://box.kancloud.cn/2015-09-15_55f7eb5a199f6.png)
“EXISTS”运算子判断条件是否成立的依据比较不一样，如果子查询有任何纪录资料回传，条件就算成立：
[](http://box.kancloud.cn/2015-09-15_55f7eb5a77c2b.png)
“EXISTS”运算子通常会在使用关联子查询中：
[](http://box.kancloud.cn/2015-09-15_55f7eb5aabb74.png)
“EXISTS”与“NOT”一起使用时，就可以完成下列的查询需求：
[](http://box.kancloud.cn/2015-09-15_55f7eb5b43fba.png)
# 7 子查询与结合查询
子查询的应用通常可以简化许多工作，而一些子查询完成的工作，也可以改用其它的作法来完成。例如下列查询“所有国家首都名称”的叙述：
[](http://box.kancloud.cn/2015-09-15_55f7eb60a9d82.png)
把上列的需求改用结合查询来完成的话，其实看起来会更简单一些：
[](http://box.kancloud.cn/2015-09-15_55f7eb76002c6.png)
如果需求换成查询“不是首都的城市名称”，可以使用下列搭配子查询的作法：
[](http://box.kancloud.cn/2015-09-15_55f7eb767cbf0.png)
上列的需求要改成使用结合查询来完成的话，会比较不一样。所以要先了解使用“LEFT JOIN”结合查询的效果：
[](http://box.kancloud.cn/2015-09-15_55f7eb7793072.png)
根据“LEFT JOIN”结合查询产生的效果，为这个结合查询设定适当的条件，就可以完成查询“不是首都的城市名称”：
[](http://box.kancloud.cn/2015-09-15_55f7eb786f8ff.png)
';