舉一些子查詢的例子方便大家容易理解

mysql> select m,n from (select m2+1 as m, n2 as n from t2 where m2 > 2) as t;
+------+------+
| m    | n    |
+------+------+
|    4 | c    |
|    5 | d    |
+------+------+
2 rows in set (0.00 sec)

這個例子中子查詢就是select m2+1 as m, n2 as n from t2 where m2 > 2
以這個子查詢的結果當作表t來查詢，這種放在from後面的子查詢又稱為衍生表。

子查詢的類型可以分為幾種：

1. 按照返回結果集區分子查詢
   純量子查詢：只返回一個單一值的子查詢

mysql> select m1 from t1 limit1;
+------+
| m1   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select min(m2) from t2;
+---------+
| min(m2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

行子查詢：返回一筆紀錄的子查詢，包含多個列

mysql> select m2,n2 from t2 limit 1;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

列子查詢：查詢出一個列的資料，不過這個列的資料需要包含多筆紀錄(只有一筆就是純量了)

mysql> select m2 from t2;
+------+
| m2   |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

表子查詢：查詢出多筆紀錄且有多個列。

mysql> select m2,n2 from t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

2. 按與外層查詢的關係來區分子查詢
   不相關子查詢：如果子查詢可以單獨運行，不需要外層查詢的值，就是不相關子查詢，前面提到的都是。
   相關子查詢：子查詢需要外層查詢的值。

mysql> select * from t1 where m1 in (select m2 from t2 where n1 = n2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.02 sec)

子查詢select m2 from t2 where n1 = n2
由於n1是t1的列，所以這是個相關子查詢(依賴外層查詢的列)。

3. 子查詢在布林運算式中的使用
   使用=、>、<、>=、<=、!=等作為布林運算式的符號

mysql> select * from t1 where m1 < (select min(m2) from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.01 sec)

mysql> select * from t1 where m1 < (select max(m2) from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where (m1,n1) = (select m2,n2 from t2 limit 1);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

要注意的是這裡一定只能是純量子查詢(單一值)或是行子查詢(一筆紀錄)喔！！

[not]in/any/some/all子查詢
當子查詢的結果有多筆紀錄的時候使用如下：

mysql> select * from t1 where (m1,n1) in (select m2,n2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

找出t1的m1,n1列存在於子查詢select m2,n2 from t2的集合中

mysql> select * from t1 where m1 > any(select m2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

只要子查詢select m2 from t2集合中有紀錄比t1.m1還要小，就是true，返回紀錄。

mysql> select * from t1 where m1 > all(select m2 from t2);
Empty set (0.00 sec)

只要子查詢select m2 from t2集合中所有的紀錄都比t1.m1還要小，就是true，返回紀錄。
這邊可以看到並不符合，所以返回空集合。

mysql> select * from t1 where exists (select m2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

只要子查詢select m2 from t2集合中有一筆紀錄就是true，就返回所有t1紀錄。