一个SQL语句的奇怪现象请大家解释

表a

col1 col2 col3
1 'a'
2 'b'
3 'c'

表b

col4 col5 col6
1
2
3

SELECT *
FROM a
WHERE col1 NOT IN (
SELECT col2
FROM b);

该语句不报错

注意col2并非表b中字段

并能得到结果

虽然并非是你想得到的

请大家解释

是否是在where操作时SQL引擎将操作涉及的表进行了虚拟合并

从而对列名失去了判断能力

---------------------------------------------------------------

SQL> select * from aa;

ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5
SQL> select * from bb;

A B
- ----------
SQL> select * from aa where id not in (select fid from bb);

ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5

相反:
SQL> select * from aa where id not in null;

ID FID
-- ---

猜测not in 条件,条件没有取到值,但它永远不为空。
若in 条件,则因条件没有值,所以查询空记录集

看来是本人理解错误了,
SQL> select * from aa where id in (select id from bb);

ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5

7 rows selected

SQL> select * from aa where id in (select id from dual);

ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5

bb与dual也是同为虚表作用,id其实也是从主表取出.
---------------------------------------------------------------

我试了一下
应该是首先判断是否为表b的字段
如果是则以b的字段查询
否则再判断是都是外层(表a)字段
然后执行
所以贴主的那句展开应该是

SELECT *
FROM a
WHERE col1 NOT IN (
SELECT col2
FROM b,a);
或者
SELECT *
FROM a
WHERE col1 NOT IN (
SELECT a.col2
FROM b);

---------------------------------------------------------------

SELECT *
FROM a
WHERE col1 NOT IN (
SELECT col2
FROM b);

其实就是:

SELECT *
FROM a
WHERE col1 NOT IN (
SELECT a.col2
FROM b);

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus