子查询 子查询是一个嵌套在外层语句中的完整的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); ~~~