问题描述

有开发问我这样一个问题：

mysql> select * from aaa;
+----+---------------------+----------+---------------------+
| id | dt                  | name     | dtt                 |
+----+---------------------+----------+---------------------+
|  1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 |
+----+---------------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from bbb;
+----+---------------------+----------+
| id | dt                  | name     |
+----+---------------------+----------+
|  1 | 2019-01-14 18:19:19 | aaaaaaaa |
|  2 | 2019-01-14 18:20:49 | aaaaaaaa |
+----+---------------------+----------+
2 rows in set (0.00 sec)

mysql> select * from aaa where id in (select id from bbb where dtt<now());
+----+---------------------+----------+---------------------+
| id | dt                  | name     | dtt                 |
+----+---------------------+----------+---------------------+
|  1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 |
+----+---------------------+----------+---------------------+
1 row in set (0.00 sec)

上面内容里dtt这个字段在bbb表中并不存在，但是在外表中存在，但是为什么不报错反而查出来结果了呢？

原因

我请PostgreSQL的同事也做了同样的操作，也是不报错的；
这个在SQLServer和Oracle里面同样成立，why？
一般规则是，语句中的列名由FROM子句中引用的表隐式地限定在同一级别。如果子查询的FROM子句中引用的表中不存在列，则由外部查询的FROM子句中引用的表隐式地限定列。
即：在块结构语言计算子查询时，它开始在本地查找以解析列名。如果失败，则转到外部范围，直到找到具有该名称的列或失败为止。

风险

如果有开发这样执行delete语句，可能会导致全表被误删！

mysql> delete from aaa where id in (select id from bbb where dtt<now());

正确又安全的写法

带上表名

mysql> select aaa.* from aaa where aaa.id in (select bbb.id from bbb where bbb.dtt<now());
mysql> ERROR 1054 (42S22): Unknown column 'bbb.dtt' in 'where clause'

参考

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178050(v=sql.105)
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4cfebb2e-6fdf-4ebf-9a6f-ee1911615eba/query-returning-wrong-data-even-inner-subquery-has-syntax-error?forum=transactsql