By Trudy Pelzer
翻译：guipei
随着mysql 4.1版的发布，现在有两种方法可以使用一个单独的查询从多个数据表中取得数据：使用连接(join)或者使用子查询(subquery)。我们来举例说明，假设你有一下的表格：
CREATE TABLE clients (
clno INT,
fname VARCHAR(15),
lname VARCHAR(15),
job VARCHAR(15),
account_balance DECIMAL(7,2));
INSERT INTO clients VALUES
(10, 'sam','smith','auditor',5525.75),
(20,'james','jones','manager',8960.25);
CREATE TABLE firms (
clno INT,
company VARCHAR(15),
city VARCHAR(15));
INSERT INTO firms VALUES
(10,'abc co','leduc'),
(20,'def ltd','nisku'),
(30,'ghi inc','nisku');
下面我们使用连接(join)方法取得client表格中id为10的数据。
SELECT
fname, lname, city, job, company, account_balance
FROM clients c, firms f
WHERE c.clno = f.clno
AND c.clno = 10;
但是你并非总是可以通过使用连接(join)方式查询你所需要的数据。举一个例子，假如你需要最大的帐户表中的客户信息。下面的查询，看起来好像应该可以正确返回你所需要的数据，但是实际上会发生错误：
SELECT
fname, lname, city, job, company, account_balance
FROM clients c, firms f
WHERE c.clno = f.clno
AND c.account_balance = MAX(c.account_balance);
错误的原因是：invalid use of group function，就是这个聚合函数，Max，不允许出现在where子句当中。这也就是子查询诞生的原因，在本文当中，我将简短的描述添加到mysql数据库系统的子查询功能。
子查询就是包含在括号中选择语句
简单的将，子查询就是一个被写在另一个sql语句（一般情况下，是select语句，但是不总是这样）中的查询语句。判断是否是子查询的方式是看查询语句是否在括号中间，下面是一个例子。
SELECT * FROM clients WHERE clno IN -- outer query
(SELECT clno FROM firms WHERE city = 'leduc'); -- inner query
SELECT * FROM clients WHERE clno IN -- 外部查询
(SELECT clno FROM firms WHERE city = 'leduc'); -- 内部查询
这个查询将会返回表clients中clno中所有城市为’leduc’的记录。为了得到结果，dbms首先会取得内部查询的结果，查找所有 表 clno中所有城市为‘leduc’的记录。然后比较表clients，返回符合clno值的每一行。因为只有一个值符合子查询，实际上，等效于下面语句：
SELECT * FROM clients WHERE clno = 10;
当然，这个查询语句也可以写成以下方式：
SELECT
c.clno, fname, lname, job, account_balance
FROM clients c INNER JOIN firms f USING (clno)
WHERE city = 'leduc';
目前，就上面提到的问题，可以通过子查询解决（查询这些客户clno值最大的记录）：
SELECT fname, lname FROM clients WHERE clno =
(SELECT MAX(clno) FROM firms);
如果内部查询返回空结果集，子查询会返回错误，举例说明，参看下面查询：
SELECT * FROM clients WHERE clno =
(SELECT clno FROM firms WHERE city = 'gibbons');
如果内部查询单独运行的话，很显然结果为空：因为这个表中没有city等于’gibbons’的记录。但是空的结果集不能作为值来比较。SQL 标准要求子查询的结果集，空的结果集，或者为空。因为没有任何东西等于null，查询返回空消息。
一般来说，子查询是嵌套在其他的查询当中，MySql支持子查询嵌套在其他sql语句当中，这是一个很好的功能。
Types of Subqueries
子查询类型
子查询共有三种类型，区分它们的方法是根据返回结果的列数和行数。
如果一个子查询返回仅仅一列一行数据，被成为数量级子查询。一个数量级子查询可以用在任何适合数据值比较的地方（一个列值或者文本）。它常常被使用在where子句当中，跟随在比较操作符号后面。 如果子查询返回多个列，并且是一行记录的话，它被称为行级子查询。行级子查询是数量级子查询的发展，可以用在任何适合数量级子查询的地方。 最后，如果一个子查询返回多个列和多行的话，它被称为表级子查询。表级子查询可以用作涉及到表的查询操作，包含在from子句当中。它也常常被用在where子句当中，后面使用in 或者 exist或者一个确定的比较操作（确定的比较操作是一个比较操作通过使用some,all,或者any限定）。数量级子查询和表级子查询的区别非常小。经常会出现在一个数量级子查询被编写的情况下，但是子查询返回多个记录行。假设我们有两个表和一些记录数据。
INSERT INTO clients VALUES
(10, 'sam','smith','auditor',5525.75);
INSERT INTO firms VALUES
(10,'abc co','leduc'),(30,'ghi inc','nisku');
因为在这个查询中表firms有两行记录返回：
SELECT * FROM clients WHERE clno <
(SELECT clno FROM firms);
错误原因：
"Subquery returns more than 1 row"
这种情况有两个解决方法。第一个是修改查询让其用any包含，使外部查询的比较任意的值 ：
SELECT * FROM clients WHERE clno < ANY
(SELECT clno FROM firms);
在这里，第一个比较是(10<10),返回为false，但是第二个比较(10<30)，返回true,因此子查询返回true,使用的是 clno 10相比较的结果，使用any 的规则如下：
ANY返回true，如果子查询的比较操作至少有一个是是true。 ANY 返回 false,如果子查询返回为空行，或者比较任意一行都是false。ANY的同义词是SOME,使用in 等同于使用 = ANY。
第二种方法是修改查询，使用all包含子查询，比较外部的查询结果使用每一个子查询的值。
SELECT * FROM clients WHERE clno < ALL
(SELECT clno FROM firms);
在这里，第一次的比较还是返回false，而第二此返回true，最后，子查询的结果是false，所以查询返回空行。使用all的规则是：
返回true，如果子查询返回空行记录，或者子查询的每一个结果比较都为true的话。 返回false，如果子查询至少有一个比较结果返回false。Does the Subquery Return at least One Row?
子查询至少要求返回一行么？
在有些时候，查询的结果是子查询是否有一个结果返回。使用[NOT] EXISTS来判断结果集是否为空。如果你的子查询至少有一行返回，使用EXISTS 返回 true；否则，返回为false。还有一种用法，NOT EXISTS，如果子查询结果集为空，它返回为true，否则的话，返回为true。一般情况下，子查询跟随在 [NOT] EXISTS后面，以select * 开头。在这里，星号(*)不是表示列出所有的列，而是表示为列出一些列。这里有一个小例子，返回所有client表的结果。
SELECT * FROM clients WHERE EXISTS
(SELECT * FROM firms);
在本例当中，where子句返回true，因为表firms不为空。通常[NOT] EXISTS会使用在更加复杂的情况下面，假设你有一下数据表：
CREATE TABLE passengers (
name VARCHAR(15),
compartment INT);
INSERT INTO passengers VALUES ('smith',20);
INSERT INTO passengers VALUES ('jones',25);
CREATE TABLE cars (
compartment INT,
class VARCHAR(10));
INSERT INTO cars VALUES (20,'first');
下面是这中类型的查询：
SELECT * FROM cars c1 WHERE NOT EXISTS
(SELECT * FROM passengers p1 WHERE NOT EXISTS
(SELECT * FROM cars c2
WHERE c2.compartment = p1.compartment
AND c2.compartment = c1.compartment));
这个查询的作用是请求查找所有存在乘客的汽车。为了理解这个结果集的含义，我们假设Smith在20号汽车，Jones在25号汽车，但是在cars表中没有包含25号汽车。这样就会有一个乘客―Jones―乘坐一个不存在的汽车。（在标准的数据库中，不会存在这种情况，可以通过使用主键/外键来约束这两个表间的数据完整性）。这个查询中的第二个NOT EXISTS子查询，返回true对于乘客Jones。
另外，这里的一个乘客―Jones，没有乘坐20号汽车。因此第一个NOT EXISTS子查询在这里返回false。因为这里没有其他汽车被检查，这个查询的结果是空（返回零行）――这里没有汽车每一个乘客都乘坐。
子查询的其他用法
在sql标准1999中，Mysql提供了增强的子查询支持。在前面提到的行级子查询，现在支持一次比较多个列的值：
SELECT ROW ('smith', 'auditor') =
(SELECT lname, job FROM clients WHERE clno = 10);
在这个例子中，子查询包含值为'smith' 和 'auditor'的数据。这些数据和ROW的值就行比较时，他们的内容一样，因此返回1（true）。
你也可以把一个子查询，作为一个表名，放在from子句后面（就像oracle里面的用法一样，作为内部视图）：
SELECT * FROM
(SELECT * FROM clients WHERE job LIKE 'a%') AS cl;
为了取得这个查询的结果，mysql服务器熟悉取得子查询的结果，然后把结果集赋予别名（cl，在本例中）。然后让外部的查询使用。实际上，上面的例子可以被分解如下：
SELECT * FROM cl;
这里cl作为一个子查询的临时结果集：
SELECT * FROM clients WHERE job LIKE 'a%';
当子查询被使用在from 子句当中，关键字as是强制使用的；中间的临时结果必须被命名，作为外部的查询被引用。
使用子查询修改数据
子查询还有这样的一个功能：用来修改数据库的数据。这就是说，你可以把子查询放在delete,insert,update或者replace语句里面，我们举例说明：
UPDATE clients SET account_balance =
(SELECT SUM(amount) FROM accounts where clno=clients.clno);
这个update语句为每一个客户修改 account_balance 数据，通过计算account表中的客户帐号。
这里有一个提示：目前不允许使用同一个表的子查询来修改这个表中的数据。
总结：
子查询在mysql 4.1版中新添加，现在支持数量、行、表的子查询。 比较操作符号= <> < <= > >=可以使用在子查询中，并且可以使用[NOT] IN 和 [NOT] EXISTS子句。 表级子查询可以使用短句ANY/SOME 或者 ALL做比较。 子查询可以用来修改数据。
本文地址：http://com.8s8s.com/it/it24687.htm