SELECT * FROM a WHERE col1 NOT IN ( SELECT b.col2 FROM b);这里oracle不校验,因为b(主语句存在这个字段),它认为是从a中取的select * from t1 where b not in (select b from dual); 一样可以
select * from t1 where b not in (select b from dual); 中,b是t1的字段
好贴! SELECT * FROM a WHERE col1 NOT IN ( SELECT col2 FROM b);其实就是:SELECT * FROM a WHERE col1 NOT IN ( SELECT a.col2 FROM b);这下可以理解了吧?
既然大家有兴趣,大家可知道以下语句的返回值 SQL> create table t1(a int,b int);Table createdSQL> insert into t1 values(1,2);1 row insertedSQL> insert into t1 values(1,3);1 row insertedSQL> insert into t1 values(1,4);1 row insertedSQL> commit;SQL> select * from t1 where b=(select max(b) from dual);
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 fid from bb);ID FID -- ---用in的话,什么也取不到
原来是all!也就是说select max(b) from dual 这个子查询,等于是对每条记录使用了max,所以与select b from dual的作用一样!呵呵.....
:penitent所有的记录,select * from t1 where b=(select max(b) from dual);max(b) 在这里的意思是max(b) of current record , which means b = b
看来是本人理解错误了, 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 57 rows selectedSQL> 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 5bb与dual也是同为虚表作用,id其实也是从主表取出.
如果表b中也有col2会怎样呢?
我试了一下 应该是首先判断是否为表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);
FROM a
WHERE col1 NOT IN (
SELECT b.col2
FROM b);这里oracle不校验,因为b(主语句存在这个字段),它认为是从a中取的select * from t1 where b not in (select b from dual);
一样可以
中,b是t1的字段
SELECT *
FROM a
WHERE col1 NOT IN (
SELECT col2
FROM b);其实就是:SELECT *
FROM a
WHERE col1 NOT IN (
SELECT a.col2
FROM b);这下可以理解了吧?
SQL> create table t1(a int,b int);Table createdSQL> insert into t1 values(1,2);1 row insertedSQL> insert into t1 values(1,3);1 row insertedSQL> insert into t1 values(1,4);1 row insertedSQL> commit;SQL> select * from t1 where b=(select max(b) from dual);
-- ---
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 条件,则因条件没有值,所以查询空记录集
-- ---用in的话,什么也取不到
你拿id与fid比较当然什么都没有 Lastdrop不要凭想象--------------------------
其实,这类问题是没有意义的,不能这么写,这么写就是书写的错误了
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 57 rows selectedSQL> 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 5bb与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);