子查詢 子查詢是一個嵌套在外層語句中的完整的select語句，通常用()括起來。可以范圍一個單一值，一行值，一個表格。 通常子查詢的方式可以簡化復雜join表連接查詢和union結果合并查詢，提高了語句的可讀性。 ~~~ select (select s1 from t2) from t1; select * from t1 where column1=(select max(column2) from t2); delete from t1 where column1 in (select column1 from t2); select s1 from t1 where s1 = any(select s1 from s2); ###any 是任意一個 all是所有的 select s1 from t1 where s1 in (select s1 from t2); ~~~ ~~~ SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2); SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2); SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2); SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1; SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id); ~~~ **exists ** exists對外表用loop逐條查詢，每次查詢都會查看exists的條件語句，當 exists里的條件語句能夠返回記錄行時(無論記錄行是的多少，只要能返回)，條件就為真，返回當前loop到的這條記錄，反之如果exists里的條 件語句不能返回記錄行，則當前loop到的這條記錄被丟棄，exists的條件就像一個bool條件，當能返回結果集則為true，不能返回結果集則為 false ~~~ select * from A where not exists (select * from B where B.id = A.id); select * from temp where exists(select 1 from temp1 where temp1.id=temp.id); ~~~