mysql中带有in的子查询，子查询select表1存在但表2不存在的字段，出现的奇怪输出

Question

0、数据库版本

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `student_id` int(11),
  `course` varchar(20)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', '标题');
INSERT INTO `course` VALUES ('2', '标题');
INSERT INTO `course` VALUES ('3', '标题');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11),
  `name` varchar(20)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', 'lily');
INSERT INTO `student` VALUES ('2', 'lucy');
INSERT INTO `student` VALUES ('3', 'nacy');
INSERT INTO `student` VALUES ('4', 'hanmeimei');

1、先看两个表的数据

2、奇怪的sql查询结果

3、为什么course中不存在id或者name字段，子查询不报错；

4、实际上是不是这样的

5、整条语句中子查询返回的是什么？

---

更新2楼朋友的代码结果

Answer 1

Mysql 子查询的实现并不是我们想象的那样。

select * from student where id in (select student_id from course);

对于上面的sql，我们一般会认为是这样执行:
猜想1:

select student_id from course;
result: 1,2,3

select * from student where id in(1,2,3)

事实上不是这样，Mysql会把外层表压入到子查询中，具体的执行计划可以使用explain extended查看：

EXPLAIN EXTENDED
select * from student where id in (select student_id from course);

SHOW WARNINGS;

select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name` 
from `test`.`student` 
where <in_optimizer>(
`test`.`student`.`id`,<exists>(select 1 from `test`.`course` where (<cache>(`test`.`student`.`id`) = `test`.`course`.`student_id`)));

Mysql处理后等价于：

select * from student where exists (select 1 from course where course.student_id = student.id);

查看执行计划，可以看到mysql对于student表全表扫描，然后按照id逐条执行子查询。这和我们的猜想1完全相反。

到现在并没有解释LZ的问题，我们可以猜想下。
猜想2:既然是先全表扫描然后逐条子查询，是不是子查询的变量都是来自全表扫描时的局部变量？我们再看一下执行计划：

EXPLAIN EXTENDED
select * from student where id in (select id from course);

show WARNINGS;

select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name` from `test`.`student` where <in_optimizer>(`test`.`student`.`id`,<exists>(select 1 from `test`.`course` where (<cache>(`test`.`student`.`id`) = `test`.`student`.`id`)));

通过查询计划可以清晰的看到，真正执行的时候，子查询的变量都是全表扫描时的局部变量<cache>student.id，这也就解释了“为什么course中不存在id或者name字段，子查询不报错”，是因为查询的优化，导致根本不是使用的course的id或者name字段。

Answer 2

正确写法：

SELECT * FROM student
WHERE id IN (
    SELECT student_id FROM course
);

SELECT * FROM student
WHERE name IN (
    SELECT course FROM course
);

要验证第4个问题的猜想，可以这样写，然后看看输出的SQL结果：

EXPLAIN
SELECT * FROM student
WHERE name IN (
    SELECT name FROM course
);
SHOW WARNINGS;

Answer 3

应该是先去子表找该字段，找不到会使用外表的该字段，找到了就用，都找不到才会报错的吧