我要评论子查询就是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;
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论