数据库版本:SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
情况1:SQL> create table a1(id number);
表已创建。
SQL> create table a2(id number);
表已创建。
SQL> insert into a1 values(1);
已创建 1 行。
SQL> insert into a1 values(2);
已创建 1 行。
SQL> insert into a1 values(1);
已创建 1 行。
SQL> insert into a1 values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into a2 values(1);
已创建 1 行。
SQL> insert into a2 values(3);
已创建 1 行。
SQL> insert into a2 values(1);
已创建 1 行。
SQL> insert into a2 values(5);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from a1;
ID
----------
1
2
1
3
a1表ID=1有两条数据。
SQL> select * from a2;
ID
----------
1
3
1
5
a2表ID=1的也有两条数据。
SQL> select a1.id,a2.id from a1,a2 where a1.id=a2.id;
ID ID
---------- ----------
1 1
1 1
3 3
1 1
1 1
a1表和a2表ID=1的都有两条数据,那么内连接的结果就是将a1表和a2表ID=1的结果进行了笛卡尔集,所以返回的结果有4条ID=1的记录。
SQL> select a1.id,a2.id from a1 left outer join a2
2 on a1.id=a2.id;
ID ID
---------- ----------
1 1
1 1
3 3
1 1
1 1
2
已选择6行。
a1表只有4条记录,理论上左连接的结果应该只有4条,但是这里的查询出现了6条记录,原因就在于a1表和a2表都有重复记录,两个表ID=1的数据做了笛卡尔集,所以多了两条数据出来。
如果只有一张表有重复记录,笛卡尔集也不会出现多出的数据:
SQL> delete from a2 where id=1;
已删除2行。
SQL> select a1.id,a2.id from a1 left outer join a2
2 on a1.id=a2.id;
ID ID
---------- ----------
3 3
1
1
2
由此看来,所谓的连接其实是把多个表的相同记录来做笛卡尔集,如果两个表不是同时的重复相同的数据,那么结果都将是我们满意的结果,如果同时重复某条关联数据,那么将收到成倍的返回数据。
情况2:对于not in来说会出现如下情况:
SQL> select * from a1;
ID
----------
1
2
1
3
SQL> select * from a2;
ID
----------
1
3
1
5
SQL> insert into a1 values (1);
已创建 1 行。
SQL>
SQL>select * from a1 where id not in (select id from a2);
ID
----------
2
SQL> select a1.id,a2.id from a1 left outer join a2 on a1.id=a2.id;
ID ID
---------- ----------
1 1
1 1
1 1
3 3
1 1
1 1
1 1
2
已选择8行。
SQL> select a1.id,a2.id from a1 left outer join a2 on a1.id=a2.id 2 where a2.id is null;
ID ID
---------- ----------
2
虽然使用not in和left outer join的方式的结果是相同的,但是从left outer join的中间结果可以看出,由于两个表都有ID=1的重复记录,所以中间结果会多很多数据出来,这也会导致SQL性能的下降。如果where子句的过滤条件是is not null,那么同样会返回包含笛卡尔集的返回结果,为了更好的性能和准确的数据返回,可以先使用distinct的方法至少去掉一张表关联字段的重复值,之后再进行相关的not in或者联接操作。
情况3:对于具有Null值,not in的处理:
SQL> select * from a1;
ID
----------
1
2
1
3
1
SQL> select * from a2;
ID
----------
1
3
1
5
SQL> insert into a2 values(null);
已创建 1 行。
SQL> select * from a1 where id not in (select id from a2);
未选定行
如果a2表中包含空值,那么使用not in将没有任何值返回,因为Null不是一个值,不能在IN ,NOT IN,=中出现。
SQL> select a1.id,a2.id from a1 left outer join a2 on a1.id=a2.id
2 where a2.id is null;
ID ID
---------- ----------
2
使用left outer join的方式就可以接受a2表有NULL值的情况,因为使用这种方式将两表的值进行关联(非NOT IN ,IN ,=),NULL关联不上自然不会出现。
以上是到目前为止遇到过的有关联接和NOT IN之间遇到的特殊问题,记录下来以备查看,如有类似问题继续补充!
--end--