为什么查询不会因子查询中不存在的列而失败? [英] Why query does not fail with nonexistent column in subquery?

问题描述

我在查询中拼写错误，并遇到了MySQL的奇怪行为.

I misspelled in my query and faced with MySQL's strange behaviour.

create table aaa (id bigint auto_increment primary key, 
                  amount int not null, 
                  other_column varchar(20)) engine=InnoDB
create table bbb (aaa_id bigint not null, 
                  comment varchar(200), 
                  key(aaa_id)) engine=InnoDB; 
insert into aaa(other_column, amount) values ('hello, world', 12), 
                                             ('second string', 15), 
                                             ('one more', 100);
insert into bbb value (2, 'no 2s!');

以下查询产生null结果(我键入了'id'而不是'aaa_id'):

The following query produces null result (I typed 'id' instead of 'aaa_id'):

select sum(amount) from aaa where id not in (select id from bbb);

我想，也许'id'对MySQL有特殊含义".但是，以下查询正常执行并返回127(就像子查询返回空结果一样):

"Maybe 'id' has special meaning for MySQL", I thought. But, the following query executes normally and returns 127 (as if subquery returns empty result):

select sum(amount) from aaa where id not in (select other_column from bbb);

以下查询产生预期的结果:第一个查询失败，显示Unknown column 'id2' in 'field list'，第二个查询返回112:

The following queries produce expected result: first one fails with Unknown column 'id2' in 'field list', and the second one returns 112:

select sum(amount) from aaa where id not in (select id2 from bbb);
select sum(amount) from aaa where id not in (select aaa_id from bbb);

可以看出，如果外部查询中存在该列，MySQL将以某种方式执行子查询.但是子查询中这些列的含义是什么?

As it can be seen, MySQL somehow executes subquery if the column exists in the outer query. But what the meaning of those columns in the subquery?

在5.1.70和5.5上进行了测试.

Tested on 5.1.70 and 5.5.

推荐答案

此查询:

select sum(amount)
from aaa
where id not in (select id from bbb);

被解释为:

select sum(aaa.amount)
from aaa
where aaa.id not in (select aaa.id from bbb);

因为bbb.id不存在.编写SQL时，建议您始终使用表别名.您认为自己正在写的查询:

because bbb.id does not exist. When writing SQL, I suggest that you always use table aliases. The query that you thought you were writing:

select sum(aaa.amount)
from aaa
where aaa.id not in (select bbb.id from bbb);

会产生您期望的错误.

这篇关于为什么查询不会因子查询中不存在的列而失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！