每日MySQL之026：MySQL的子查询(subquery)

子查询就是SELECT语句里的子SELECT语句，一个子查询可以返回一个单一值（scalar）、一行、一列或者一个表

1. scalar subquery

返回一个值，最简单的子查询，示例1：

mysql> CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
mysql> INSERT INTO t1 VALUES(100, 'abcde');
mysql> SELECT (SELECT s2 FROM t1);
+---------------------+
| (SELECT s2 FROM t1) |
+---------------------+
| abcde               |
+---------------------+
1 row in set (0.05 sec)

示例2：

mysql> CREATE TABLE t1 (s1 INT);
mysql> INSERT INTO t1 VALUES (1);
mysql> CREATE TABLE t2 (s1 INT);
mysql> INSERT INTO t2 VALUES (2);
mysql> SELECT (SELECT s1 FROM t2) FROM t1;
+---------------------+
| (SELECT s1 FROM t2) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

2. 带有 ANY, IN, 或者 SOME 的子查询 语法如下：
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
其中 comparison_operator 是下列之一：

= > < >= <= <> !=

其中ANY和SOME的含义和用法完全一样，而 IN 相当于 "= ANY", 因此，下面三种语法完全相同：

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 = SOME (SELECT s1 FROM t2);

3. 带有 ALL 的子查询 语法如下：

operand comparison_operator ALL (subquery)

示例：

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2); 

"NOT IN" 和 "<> ALL" 效果一样，下面的两条SQL等价：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

4. Row Subqueries 如果子查询的结果返回一行，那么称这个子查询为 Row Subqueries

示例如下，注意，下面的SQL要求子查询最多只能返回一行记录，否则会报错 ERROR 1242 (21000): Subquery returns more than 1 row：

SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

下面的SQL则返回所有即在t1中，又在t2中的记录：

SELECT column1,column2,column3
  FROM t1
  WHERE (column1,column2,column3) IN
         (SELECT column1,column2,column3 FROM t2);

5. 带有 EXISTS 或 NOT EXISTS 的子查询 如果子查询有返回记录，则EXISTS子查询为真，否则为假：
If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

一般来讲，EXISTS子查询里写的都是SELECT *， 不过实际上你也可以写成SELECT 5，或者SEELCT column1，MySQL都会忽略SELECT的列的，上面的SQL语句和下面的是等同的：

这里不管查询的结果是不是为NULL，只要有记录，EXISTS就返回真。

6. Derived Tables Derived table (Subqueries in the FROM Clause) 是FROM语句后的子查询构成的表，语法如下

SELECT ... FROM (subquery) [AS] tbl_name ...

[AS] tbl_name是必须的，因为 FROM 后面接的一定是个表名，示例：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;