mysql子查询表中列不存在，正常执行语句

引言

实际开发中，经常会编造复合查询语句用于跨表查询，以select子查询、join on语句为主，今天来探讨一个神奇的sql语句。

背景

这里有两个表test1、test2，现在需要查出所有test1表中id在test2表存在的记录

• test1

id	name	tel
1	张三	1801111111111
2	李四	1371111111111

• test2

t_id	sex
1	男

满足需求

根据背景要求，很快可以想到使用test1作为基表，test2作为子表进行复合查询，sql语句如下：

select * from test1 where id in (select t_id from test2);

语句查询结果：

提出假设

根据上面的做法，确实很快可以得出理想的结果，但是人总会有手抖的时候。思考下，如果这时，我以为两个表的主键名称都叫id（毕竟正常情况下，表连接的键名称是一致的）。然后语句写成这样：

select * from test1 where id in (select id from test2);

但实际上，这个id字段并不存在test2表中，按常理说，这个子句应该会因为字段不存在报错，然后整个语句查询失败，然而，语句查询结果：

啊咧？不是报错？

解析

1. 解析非正常语句

别急，看看具体的执行计划，使用explain extended查看

explain extended
select * from test1 where id in (select id from test2);
	
SHOW WARNINGS;
select `booksys`.`test1`.`id` AS `id`,`booksys`.`test1`.`name` AS `name`,`booksys`.`test1`.`tel` AS `tel` from `booksys`.`test1` semi join (`booksys`.`test2`) where 1

等价于如下语句：

SELECT semi.* FROM `booksys`.`test1` semi JOIN ( `booksys`.`test2` ) WHERE 1

2. 大胆猜想正确语句的具体执行计划

原来，上面的语句在数据查询优化时会翻译成内连接，由于字段id不存在test2表中，然后就会去找外层表test1，找到就用该字段，也就是这个1 等价于 test1.id = test1.id

那么，满足需求的语句select * from test1 where id in (select t_id from test2);解析后应该是这样的：select semi.* from test1 join test2 where test1.id = test2.t_id
查看具体的执行计划是否满足上述猜想：

SHOW WARNINGS;
select `booksys`.`test1`.`id` AS `id`,`booksys`.`test1`.`name` AS `name`,`booksys`.`test1`.`tel` AS `tel` from `booksys`.`test2` join `booksys`.`test1` where (`booksys`.`test1`.`id` = `booksys`.`test2`.`t_id`)

3. 最后一种情况猜想

由此可见，猜想正确，那么如果子查询字段在子表和外层表都找不到，应该会报错了吧！
话不多说，直接执行语句：

select * from test1 where id in (select p_id from test2);

语句查询结果：

结论

综上所述，子查询的字段查找顺序：子表->外层表。只有两个表都找不到时，才会报错。因此，在实际开发中，若碰到查询结果与预想结果不一致时，可以去追查具体的执行计划。只有眼见为实，才能知道这种情况为何发生。