oracle delete inner join,Oracle数据库联接(inner join ,outer join)和NOT IN的特殊情况

数据库版本：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--