MySQL⾥⾯的⼦查询
⼀、⼦查询定义
定义：
⼦查询允许把⼀个查询嵌套在另⼀个查询当中。
⼦查询，⼜叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。
⼦查询可以包含普通lect可以包括的任何⼦句，⽐如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之⼀：lect、inrt、update、delete、t或者do。贺卡格式范文图片
⼦查询的位置：智力低和开窍晚区别
lect 中、from 后、where 中.group by 和order by 中⽆实⽤意义。
⼆、⼦查询分类
⼦查询分为如下⼏类：
1. 标量⼦查询：返回单⼀值的标量，最简单的形式。
2. 列⼦查询：返回的结果集是 N ⾏⼀列。
3. ⾏⼦查询：返回的结果集是⼀⾏ N 列。
4. 表⼦查询：返回的结果集是 N ⾏ N 列。
可以使⽤的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS
⼀个⼦查询会返回⼀个标量（就⼀个值）、⼀个⾏、⼀个列或⼀个表，这些⼦查询称之为标量、⾏、列和表⼦查询。
如果⼦查询返回⼀个标量值（就⼀个值），那么外部查询就可以使⽤：=、>、<、>=、<=和<>符号进⾏⽐较判断；如果⼦查询返回的不是⼀个标量值，⽽外部查询使⽤了⽐较符和⼦查询的结果进⾏了⽐较，那么就会抛出异常。
1. 标量⼦查询：
是指⼦查询返回的是单⼀值的标量，如⼀个数字或⼀个字符串，也是⼦查询中最简单的返回形式。可以使⽤ = > < >= <= <> 这些操作符对⼦查询的标量结果进⾏⽐较，通常⼦查询的位置在⽐较式的右侧
⽰例：
SELECT * FROM article WHERE uid = (SELECT uid FROM ur WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)
2. MySQL 列⼦查询：
指⼦查询返回的结果集是 N ⾏⼀列，该结果通常来⾃对表的某个字段查询返回。
可以使⽤ = > < >= <= <> 这些操作符对⼦查询的标量结果进⾏⽐较，通常⼦查询的位置在⽐较式的右侧
可以使⽤ IN、ANY、SOME 和 ALL 操作符，不能直接使⽤ = > < >= <= <> 这些⽐较标量结果的操作符。花式颠球
⽰例：
SELECT * FROM article WHERE uid IN(SELECT uid FROM ur WHERE status=1)
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)
NOT IN 是 <> ALL 的别名，⼆者相同。
特殊情况
如果 table2 为空表，则 ALL 后的结果为 TRUE；
如果⼦查询返回如 (0,NULL,1) 这种尽管 s1 ⽐返回结果都⼤，但有空⾏的结果，则 ALL 后的结果为 UNKNOWN 。
注意：对于 table2 空表的情况，下⾯的语句均返回 NULL：
SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)
3. MySQL ⾏⼦查询：
指⼦查询返回的结果集是⼀⾏ N 列，该⼦查询的结果通常是对表的某⾏数据进⾏查询⽽返回的结果集。
例⼦：
SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
注：(1,2) 等同于 row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)
4. MySQL 表⼦查询：
指⼦查询返回的结果集是 N ⾏ N 列的⼀个表数据。
例⼦：登城
SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
三、字查询例举
1. ANY进⾏⼦查询
any关键词的意思是“对于⼦查询返回的列中的任何⼀个数值，如果⽐较结果为TRUE，就返回TRUE”。
好⽐“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意⼀个进⾏⽐较，得到TRUE时，就会返回TRUE。
lect table1.customer_id,city,count(order_id)
from table1 join table2
on table1.customer_id=table2.customer_id
where table1.customer_id<>'tx' and table1.customer_id<>'9you'
group by customer_id
having count(order_id) >
练习做某事any (
lect count(order_id)
from table2
where customer_id='tx' or customer_id='9you'
group by customer_id);
any的意思⽐较好明⽩，直译就是任意⼀个，只要条件满⾜任意的⼀个，就返回TRUE。
2. 使⽤IN进⾏⼦查询
使⽤in进⾏⼦查询，这个我们在⽇常写sql的时候是经常遇到的。in的意思就是指定的⼀个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。
in是“=any”的别名，在使⽤“=any”的地⽅，我们都可以使⽤“in”来进⾏替换。
有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是⼀个意思。
玻璃毛细管 3. 使⽤SOME进⾏⼦查询
some是any的别名，⽤的⽐较少。
4. 使⽤ALL进⾏⼦查询
all必须与⽐较操作符⼀起使⽤。all的意思是“对于⼦查询返回的列中的所有值，如果⽐较结果为TRUE，则返回TRUE”。
好⽐“10 >all(2, 4, 5, 1)”，由于10⼤于集合中的所有值，所以这条判断就返回TRUE；⽽如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10⼩于20，所以该判断就会返回FALSE。
<>all的同义词是not in，表⽰不等于集合中的所有值，这个很容易和<>any搞混，平时多留点⼼就好了。
5.标量⼦查询
根据⼦查询返回值的数量，将⼦查询可以分为标量⼦查询和多值⼦查询。在使⽤⽐较符进⾏⼦查询时，就要求必须是标量⼦查询；如果是多值⼦查询时，使⽤⽐较符，就会抛出异常。
6. 多值⼦查询
与标量⼦查询对应的就是多值⼦查询了，多值⼦查询会返回⼀列、⼀⾏或者⼀个表，它们组成⼀个集合。我们⼀般使⽤的any、in、all 和some等词，将外部查询与⼦查询的结果进⾏判断。如果将any、in、all和some等词与标量⼦查询，就会得到空的结果。
7. 独⽴⼦查询
独⽴⼦查询是不依赖外部查询⽽运⾏的⼦查询。什么叫依赖外部查询？先看下⾯两个sql语句。
sql语句1：获得所有hangzhou顾客的订单号。
lect order_id
from table2
where customer_id in
(lect customer_id
from table1
where city='hangzhou');
sql语句2：获得城市为hangzhou，并且存在订单的⽤户。
lect *
from table1
where city='hangzhou' and exists
(lect *
from table2
where table1.customer_id=table2.customer_id);
上⾯的两条sql语句，虽然例⼦举的有点不是很恰当，但是⾜以说明这⾥的问题了。
对于sql语句1，我们将⼦查询单独复制出来，也是可以单独执⾏的，就是⼦查询与外部查询没有任何关系。
对于sql语句2，我们将⼦查询单独复制出来，就⽆法单独执⾏了，由于sql语句2的⼦查询依赖外部查询的某些字段，这就导致⼦查询就依赖外部查询，就产⽣了相关性。
移动怎么取消套餐 对于⼦查询，很多时候都会考虑到效率的问题。当我们执⾏⼀个lect语句时，可以加上explain关键字，⽤来查看查询类型，查询时使⽤的索引以及其它等等信息。⽐如这么⽤：
explain lect order_id
from table2
where customer_id in
(lect customer_id
解析域名
from table1
where city='hangzhou');
使⽤独⽴⼦查询，如果⼦查询部分对集合的最⼤遍历次数为n，外部查询的最⼤遍历次数为m时，我们可以记为：O(m+n)。⽽如果使⽤相关⼦查询，它的遍历次数可能会达到O(m+m*n)。可以看到，效率就会成倍的下降；所以，⼤伙在使⽤⼦查询时，⼀定要考虑到⼦查询的相关性。
8.相关⼦查询
相关⼦查询是指引⽤了外部查询列的⼦查询，即⼦查询会对外部查询的每⾏进⾏⼀次计算。但是在MySQL的内部，会进⾏动态优化，会随着情况的不同会有所不同。使⽤相关⼦查询是最容易出现性能
的地⽅。⽽关于sql语句的优化，这⼜是⼀个⾮常⼤的话题了，只能通过实际的经验积累，才能更好的去理解如何进⾏优化。
9.EXISTS谓词
EXISTS是⼀个⾮常⽜叉的谓词，它允许数据库⾼效地检查指定查询是否产⽣某些⾏。根据⼦查询是否返回⾏，该谓词返回TRUE或FALSE。与其它谓词和逻辑表达式不同的是，⽆论输⼊⼦查询是否返回⾏，EXISTS都不会返回UNKNOWN，对于EXISTS来
说，UNKNOWN就是FALSE。还是上⾯的语句，获得城市为hangzhou，并且存在订单的⽤户。
lect *
from table1
where city='hangzhou' and exists
(lect *
from table2
where table1.customer_id=table2.customer_id);
关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，⽽对于IN，除了TRUE、FALSE值外，还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理⽅式与FALSE相同，因此使⽤IN与使⽤EXISTS⼀样，SQL优化器会选择相同的执⾏计划。
说到了IN和EXISTS⼏乎是⼀样的，但是，就不得不说到NOT IN和NOT EXISTS，对于输⼊列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现的⾮常⼤了。输⼊列表包含NULL值时，IN总是返回TRUE和UNKNOWN，因此NOT IN就会得到NOT TRUE和NOT UNKNOWN，即FALSE和UNKNOWN。
10. 派⽣表
上⾯也说到了，在⼦查询返回的值中，也可能返回⼀个表，如果将⼦查询返回的虚拟表再次作为FROM⼦句的输⼊时，这就⼦查询的虚拟表就成为了⼀个派⽣表。语法结构如下：
FROM (subquery expression) AS derived_table_alias
由于派⽣表是完全的虚拟表，并没有也不可能被物理地具体化。
四、⼦查询优化
很多查询中需要使⽤⼦查询。使⽤⼦查询可以⼀次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。⼦查询可以使查询语句很灵活，但⼦查询的执⾏效率不⾼。
⼦查询时，MySQL需要为内层查询语句的查询结果建⽴⼀个临时表。然后外层查询语句再临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，⼦查询的速度会受到⼀定的影响。如果查询的数据量⽐较⼤，这种影响就会随之增⼤。
在MySQL中可以使⽤连接查询来替代⼦查询。连接查询不需要建⽴临时表，其速度⽐⼦查询要快。
使⽤连接(JOIN)来代替⼦查询
如：
例⼦1：
SELECT * FROM t1
WHERE t1.a1 NOT in (SELECT a2 FROM t2 )
优化后：
SELECT * FROM t1
LEFT JOIN t2 ON t1.a1=t2.a2
WHERE t2.a2 IS NULL
例⼦2：
SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
优化后：
SELECT * FROM article
inner join blog
on (article.title=blog.title t AND article.uid=blog.uid)
不能优化的⼦查询：
1、mysql不⽀持⼦查询合并和聚合函数⼦查询优化，mariadb对聚合函数⼦查询进⾏物化优化；
2、mysql不⽀持from⼦句⼦查询优化，mariadb对from⼦句⼦查询进⾏⼦查询上拉优化；
3、mysql和mariadb对⼦查询展开提供有限的⽀持，如对主键的操作才能进⾏上拉⼦查询优化；
4、mysql不⽀持exists⼦查询优化，mariadb对exists关联⼦查询进⾏半连接优化，对exists⾮关联⼦查询没有进⼀步进⾏优化；
5、mysql和mariadb不⽀持not exists⼦查询优化；
6、mysql和mariadb对in⼦查询，对满⾜半连接语义的查询进⾏半连接优化，再基于代价评估进⾏优化，两者对半连接的代价评估选择⽅式有差异；
7、mysql不⽀持not in⼦查询优化，mariadb对⾮关联not in⼦查询使⽤物化优化，对关联not in⼦查询不做优化；
8、mysql和mariadb对>all⾮关联⼦查询使⽤max函数，<all⾮关联⼦查询使⽤min函数，对=all和⾮关联⼦查询使⽤exists优化；
9、对>some和>any⾮关联⼦查询使⽤min函数，对<some和<any⾮关联⼦查询使⽤max函数，=any 和=some⼦查询使⽤半连接进⾏优化，对>some和>any关联⼦查询以及<some和<any关联⼦查询只有exists 优化。
