in子查询相关

原文链接（本文与原文存在部分不通）：https://blog.csdn.net/weixin_33331978/article/details/113123563

in子查询语法错误，却不影响整个查询的正确性

user表

id	name	age
1	A	9
2	B	11
3	C	15
4	D	13

blacklist表

id	name	type
1	B	1
2	C	0
3	D	1

错误流程

目的：查黑名单中年龄大于10的男用户的所有信息
正确语句

select * from user where name in (
	select name from blacklist where type = 1
) and age > 10;

错误语句

SELECT * FROM usr WHERE NAME IN (
	SELECT NAME FROM blacklist WHERE TYPE=1 AND age>10
);

把age条件加在了in查询里，可是黑名单没有age字段。

单独执行子查询报错！

执行完整语句，返回正确结果

问题分析

1、EXPLAIN EXTENDED

EXPLAIN EXTENDED 
SELECT * FROM USER WHERE NAME IN (
	SELECT NAME FROM blacklist WHERE TYPE=1 AND age>10
);

2、SHOW WARNINGS;

3、分析该语句

Field or reference 'test.USER.age' of SELECT #2 was resolved in SELECT #1

select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age` 
from `test`.`user` where <in_optimizer>(`test`.`user`.`name`,<exists>(
		select 1 from `test`.`blacklist` where(
		(`test`.`blacklist`.`type` = 1) and (`test`.`user`.`age` > 10) and (<cache>(`test`.`user`.`name`) = `test`.`blacklist`.`name`)
		)
	)
)

简单来说，数据库优化之后，每个字段都指向了正确的表。

面试回答：一般来说，我会先试一下，explain或者explain extended，然后show warnings

---

与文章对比，我explain之后，show warnings; 没有任何反应。
因此我用的explain extended，但是我的show warnings结果和他的不太一样，它的是结果出来是join，我的并没有。

可以看出，数据库把原来的查询改成了join查询，每个字段都指向了正确的表。

具体问题待确认，估计是版本问题？

in换成exists

如果in里面有语法错误没有暴露的，把in改成exists试一下：

SELECT * FROM USER WHERE EXISTS (
	SELECT NAME FROM blacklist WHERE TYPE = 1 AND age > 10
);

结果：

结果不对，并且EXISTS返回的是一个布尔值，只要其中的子查询返回行数，where条件即成立。所以，select name 换成select 1也是成立的，即结果和之前的一样。

 select * from user where EXISTS (
	 select 1 from blacklist where type = 1 and age > 10
 );

结果和之前的一样

分析

EXPLAIN EXTENDED SELECT * FROM USER WHERE EXISTS (SELECT NAME FROM blacklist WHERE TYPE = 1 AND age > 10);

SHOW WARNINGS; 

结果:

Field or reference 'test.user.age' of SELECT #2 was resolved in SELECT #1

select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age` 
	from `test`.`user` where exists(
		select `test`.`blacklist`.`name` from `test`.`blacklist` where (
			(`test`.`blacklist`.`type` = 1) and (`test`.`user`.`age` > 10)
	)
)

使用exists，同样优化掉了低级错误。如果想要得到我们预期的数据，需要在exists子查询添加一个条件

select * from user a where EXISTS (
	select 1 from blacklist where type = 1 and name = a.name and age > 10
);

exists可以看做一个循环

List result = new ArrayList<>();

List user = new ArrayList<>();
for (int i = 0; i < user.size(); i++){
	if (exists(black.type == 1 && black.name.equals(user.get(i).name)) && user.get(i).age > 10){
		result.add(user.get(i));
	}
}

IN和Exists的区别

Exists：先执行外部查询语句，然后在执行子查询，子查询中它每次都会去执行数据库的查询，执行次数等于外查询的数据数量。

In：先查询 in()子查询的数据(1次)，并且将数据放进内存里(不需要多次查询)，然后外部查询的表再根据查询的结果进行查询过滤，最后返回结果。

In 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

---

总结

in子句语法错误，可能不会影响整个语句的执行。

其他

MySQL数据库中有一个explain 命令，其主要功能是用来分析select 语句的运行效果，例如explain可以获得select语句使用的索引情况、排序的情况等等。

explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过MySQL的show warnings命令得到。

”从MySQL explain extend的输出中，我们可以看到sql的执行方式，对于分析sql还是很有帮助的”。