昨天談到Subquery...今天我就針對這個主題做點分享...
Subquery的運用很泛的, 大致有下列:

1. Subquery做為數值運算
   例如:
   SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2 WHERE column2='20091027');
   由資料表t2的column2為'20091027'的資料錄中的column1取得值再去和t1的column1欄位值做比較
   或是另一個例子:
   SELECT column1, (SELECT column1 FROM t2 WHERE column2='20091027') FROM t1 WHERE column2 = '20091027';
   傳回t1的column1資料, t2的column1資料, 而t1及t2的column2值都必需為'20091027'
2. 運用Subqueries作比較運算
   例:
   SELECT * FROM t1
   WHERE column1 = (SELECT MAX(column2) FROM t2);
   傳回t1中column1等於t2中的column2最大值的資料錄
3. 和ANY, IN, SOME合用的Subqueries
   例如:
   SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); ..t1中的s1等於t2中s1欄位的任一值
   SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2); .. 同上 = ANY
   SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2); ..t1中的s1不等於t2中s1欄位的任一值
   SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2); ..同上<>ANY
4. 和ALL合用的Subqueries
   例如:
   SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
   ..t1中s1值大於t2中的s2欄位的所有值
5. Row Subqueries
   舉例來說最明白:
   SELECT column1,column2,column3
   FROM t1
   WHERE (column1,column2,column3) IN
   (SELECT column1,column2,column3 FROM t2); 或
   SELECT column1,column2,column3
   FROM t1
   WHERE ROW(column1,column2,column3) IN
   (SELECT column1,column2,column3 FROM t2);
   兩條指令都是一樣的, 看出ROW的用法了嗎?
6. EXISTS和NOT EXISTS
   例如:
   SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
   SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);
   判斷Subquery是否有資料錄....有的話, 第一條WHERE成立, 第二條不成立
7. 在FROM中的Subqueries
   舉例來說:
   SELECT AVG(sum_column1)
   FROM (SELECT SUM(column1) AS sum_column1
   FROM t1 GROUP BY column1) AS t1;
   用了一個Subquery當成資料來源t1...可以把Subquery當成暫存資料表或檢視表
   關於Subquery, 我們先介紹到這裏, 以後我們會再來討論Subquery的優化問題(Optimization)