引言
实际开发中,经常会编造复合查询语句用于跨表查询,以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表
中,按常理说,这个子句应该会因为字段不存在报错,然后整个语句查询失败,然而,语句查询结果:
啊咧?不是报错?
解析
- 解析非正常语句
别急,看看具体的执行计划,使用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
- 大胆猜想正确语句的具体执行计划
原来,上面的语句在数据查询优化时会翻译成内连接,由于字段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`)
- 最后一种情况猜想
由此可见,猜想正确,那么如果子查询字段在子表和外层表都找不到,应该会报错了吧!
话不多说,直接执行语句:
select * from test1 where id in (select p_id from test2);
语句查询结果:
结论
综上所述,子查询的字段查找顺序:子表->外层表
。只有两个表都找不到时,才会报错。因此,在实际开发中,若碰到查询结果与预想结果不一致时,可以去追查具体的执行计划。只有眼见为实,才能知道这种情况为何发生。