1 一个叙述中的查询叙述

子查询(subquery)是一种很常见的应用，不论是查询、新增、修改或删除都有可能出现。 子查询是一个放在左右刮号中的「SELECT」叙述，而这个查询叙述会放在另一个SQL叙述中。 在执行一些工作的时候，使用子查询可以简化SQL叙述。 以查询「人口比美国多的国家」来说，你要先执行下列查询美国人口数量的叙述：

知道美国人口数量以后，再执行下列的叙述就可以传回人口比美国多的国家了：

以这样的查询来说，你要执行两次查询叙述来完成这个工作。 不过遇到类似这样的需求时：

你就可以考虑把它们写成一个叙述就可以了：

上列的范例是一种很常见的子查询应用，使用子查询的好处是不用执行多次查询就可以完成工作，这样可以简化查询的工作；对于处理资料的应用程式来说，也可以节省一些程式码。

2 WHERE、HAVING子句与子查询

子查询大部份使用在提供判断条件用的资料，在「WHERE」和「HAVING」子句中，都可能出现子查询：

2.1 比较运算子

在「WHERE」和「HAVING」子句中，你会使用许多不同的运算子来判断条件是否符合。 这些运算子中的比较运算子都可以搭配子查询来完成你的需求：

使用比较运算子的时候，你要提供一个资料让运算子判断条件是否符合。 在使用子查询提供判断用的资料时，要特别注意子查询回传的资料是否符合规定：

以下列「查询GNP最大的国家」需求来说，子查询传回的数字是「country」表格中「GNP」栏位的最大值，这个数字就给外层查询当作「WHERE」子句中的条件设定：

使用在比较运算子的子查询，在「SELECT」子句中不可以指定超过一个栏位的回传资料：

子查询也不可以回传超过一笔以上的纪录：

2.2 「IN」运算子

除了一般的比较运算子外，你可能很常使用「IN」运算子来执行多个资料的比较，你也可以使用子查询提供「IN」运算子判断的资料：

如果你想要查询「城市人口超过九百万的国家」，「IN」运算子就会出现在这类的需求中：

这类的需求，也可以改成使用子查询来完成：

以上列的范例来说，如果你用错运算子：

「IN」运算子可以视需要搭配「NOT」运算子：

2.3 其它运算子

比较运算子与子查询搭配使用时，另外还提供「ALL」、「ANY」与「SOME」三个运算子，其中「ANY」和「SOME」运算子的效果是一样的，所以只需要讨论「 ALL」与「ANY」这两个运算子：

比较运算子与「ALL」与「ANY」搭配使用时，可以完成比较特殊的查询需求，下列是两个用来测试的表格：

下列是比较运算子与「ALL」搭配使用的范例：

「ALL」运算子从字面上来看，是「全部」的意思，所以你也可以这样来看「ALL」运算子：

「ANY」运算子从字面上来看，是「任何一个」的意思，所以你也可以这样来看「ANY」运算子：

注：在MySQL中，「ANY」与「SOME」运算子的效果是一样的。

在你了解「ALL」运算子的效果以后，如果在解决你的需求时，使用了「 ALL」这样的运算子，它的效果其实跟「NOT IN」是一样的：

另外「= ANY」运算子的效果跟「IN」是一样的：

2.4 多栏位子查询

在条件设定的时候，通常会也遇到比较复杂一点的设定，例如下列这个查询「在亚洲而且政府型式为Republic的国家」叙述：

上列的条件设定，有另外一种比较简单的设定方式：

如果想要查询「跟Iraq国家同一个地区，而且跟Iraq国家的政府型式一样的国家」，因为判断条件都要经由查询才可以得到，所以你可能会写出这样的叙述：

遇到类似这样的需求时，你也可以套用这种比较简单的设定方式：

如果想要查询「每一洲GNP最高的国家」，你可以使用下列的叙述先查询每一洲最高的GNP：

跟单一资料的判断一样，子查询传回多比纪录时就要使用「IN」运算子：

3 SELECT子句与子查询

如果需要的话，子查询也可以使用在「SELECT」子句中。 以查询「国家Japan的GNP」的需求来说，下列的范例使用的是你已经熟悉的查询叙述来完成这个需求：

这类的需求也可以直接在「SELECT」子句中使用子查询传回你需要的资料：

下列的叙述可以查询「India国家占全世界人口的比例」：

4 FROM子句与子查询

子查询可以使用在「WHERE」与「HAVGIN」子句中用来设定条件，还有使用在「SELECT」子句中，用来传回需要的资料；除了这两种用法外，子查询还可以使用在「FROM」子句。 你通常会在在查询叙述的「FROM」子句中，指定需要的表格名称，有需要的话，你也可以使用子查询，这个子查询回传的结果会被当成一个「表格」：

下列的范例可以查询「亚洲GNP前十名国家」：

注：要完成上列的需求，并不需要在「FROM」子句中使用子查询，只要使用一般的查询叙述就可以了。

如果以查询「国家的官方语言与人口比例」的需求来说，你可以使用下列的查询叙述来完成这个工作：

注：要完成上列的需求，并不需要在「FROM」子句中使用子查询，使用结合查询也可以得到一样的结果。

5 资料维护与子查询

在使用「INSERT」、「UPDATE」与「DELETE」叙述执行新增、修改与删除资料时，也可以依照需要使用子查询来简化资料维护的叙述。

5.1 新增

一般来说，使用「INSERT」叙述执行新增纪录的工作时，通常是直接指定新增纪录的资料；如果你要新增的资料，可以执行一个查询来取得的话，就可以搭配子查询来简化新增纪录的工作：

以下列这个储存国家资料的表格(world.mycountry)来说：

如果你想要新增亚洲国家的资料到「mycountry」表格中，你可以使用子查询传回新增纪录需要的资料给「INSERT」叙述使用：

使用子查询提供「INSERT」叙述需要的资料，要特别注意子查询回传的栏位资料：

注：搭配「ON DUPLICATE KEY UPDATE」的效果在「资料维护、新增、索引值与ON DUPLICATE KEY UPDATE」中讨论。

MySQL另外一种新增纪录的「REPLACE」叙述，也可以使用子查询提供需要的资料：

5.2 修改

使用「UPDATE」叙述执行修改资料时，如果没有使用「WHERE」子句指定修改的条件，「UPDATE」叙述会修改表格中所有的纪录；所以执行修改纪录资料的时候，通常会使用「WHERE」子句指定修改的条件。 在「UPDATE」叙述的「WHERE」子句中，也可以使用子查询提供判断条件的资料：

如果要执行「SALES部门的员工加薪百分之五」，因为你需要先知道「SALES」部门的编号，所以你可以使用子查询传回「SALES」部门的编号，给「UPDATE」叙述中的「WHERE」子句设定部门编号的条件：

MySQL在「UPDATE」叙述中的子查询有一个特别的规定：

5.3 删除

使用「DELETE」叙述执行删除纪录时，如果没有使用「WHERE」子句指定删除的条件，「DELETE」叙述会删除表格中所有的纪录；所以执行删除纪录的时候，通常会使用「WHERE」子句指定删除的条件。 在「DELETE」叙述的「WHERE」子句中，也可以使用子查询提供判断条件的资料：

如果要执行「删除SALES部门员工」，因为你需要先知道「SALES」部门的编号，所以你可以使用子查询传回「SALES」部门的编号，给「DELETE」叙述中的「WHERE」子句设定部门编号的条件：

MySQL在「DELETE」叙述中出现的子查询有一个特别的规定：

6 关联子查询

在查询或维护的查询中，都有可能会使用子查询来提供执行叙述所需要的资料：

在 使用子查询的的时候，通常不会跟外层查询有直接的关系，也就是子查询不会使用外层查询的资料；不过遇到一些比较特殊的需求时，在「WHERE」或 「HAVING」子句中的子查询，也需要使用外层查询的资料来执行判断的工作，这样的叙述称为「关联子查询、correlated subqueries」：

在「WHERE」或「HAVING」子句中用来设定条件的子查询，可以依照需求使用像「IN」、「ANY」这些运算子来判断条件是否符合。 除了上列以经讨论的比较运算子外，还有一个「EXISTS」运算子：

「EXISTS」运算子判断条件是否成立的依据比较不一样，如果子查询有任何纪录资料回传，条件就算成立：

「EXISTS」运算子通常会在使用关联子查询中：

「EXISTS」与「NOT」一起使用时，就可以完成下列的查询需求：

7 子查询与结合查询

子查询的应用通常可以简化许多工作，而一些子查询完成的工作，也可以改用其它的作法来完成。 例如下列查询「所有国家首都名称」的叙述：

把上列的需求改用结合查询来完成的话，其实看起来会更简单一些：

如果需求换成查询「不是首都的城市名称」，可以使用下列搭配子查询的作法：

上列的需求要改成使用结合查询来完成的话，会比较不一样。 所以要先了解使用「LEFT JOIN」结合查询的效果：

根据「LEFT JOIN」结合查询产生的效果，为这个结合查询设定适当的条件，就可以完成查询「不是首都的城市名称」：

翻译自:  http://www.codedata.com.tw/database/mysql-tutorial-9-subquery/

更多实例:

WHERE clause

SELECT c.contact_id, c.last_name
FROM contacts c
WHERE c.site_name IN
   (SELECT a.site_name
    FROM address_book a
    WHERE a.address_book_id < 50);

SELECT c.contact_id, c.last_name
FROM contacts c
INNER JOIN address_book a
ON c.site_name = a.site_name
WHERE a.address_book_id < 50;

FROM clause

SELECT contacts.last_name, subquery1.total_size
FROM contacts,
 (SELECT site_name, SUM(file_size) AS total_size
  FROM pages
  GROUP BY site_name) subquery1
WHERE subquery1.site_name = contacts.site_name;

SELECT clause

SELECT p1.site_name,
  (SELECT MAX(file_size)
   FROM pages p2
   WHERE p1.site_id = p2.site_id) subquery2
FROM pages p1;

EXISTS – With SELECT Statement

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE customers.customer_id = orders.customer_id);

EXISTS – With SELECT Statement using NOT EXISTS

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
                  FROM order_details
                  WHERE customers.customer_id = orders.customer_id);

EXISTS – With INSERT Statement

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

EXISTS – With UPDATE Statement

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

EXISTS -With DELETE Statement

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

 

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (十) : Views

 

本文: MySQL入门 (九) : 子查询 Subquery