ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 中不使用NOT IN 和 NOT EXISTS的另一种方法

Oracle 中不使用NOT IN 和 NOT EXISTS的另一种方法

原创 Linux操作系统 作者:tian1982tian 时间:2011-04-17 19:42:02 0 删除 编辑

   用LEFT JOIN 代替NOT IN 或 NOT EXISTS:

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE testa
  2  (
  3   id number,
  4   value varchar2(10)
  5  );

Table created.

SQL> INSERT INTO testa VALUES(1,'a');

1 row created.

SQL> INSERT INTO testa VALUES(2,'b');

1 row created.

SQL> INSERT INTO testa VALUES(3,'c');

1 row created.

SQL> INSERT INTO testa VALUES(4,'d');

1 row created.

SQL> INSERT INTO testa VALUES(5,'e');

1 row created.

SQL> INSERT INTO testa VALUES(6,'f');

1 row created.

SQL> COMMIT;

Commit complete.
SQL> CREATE TABLE testb AS
  2  SELECT * FROM testa WHERE 1=0;

Table created.

SQL> INSERT INTO testb VALUES(2,'b');

1 row created.

SQL> INSERT INTO testb VALUES(4,'d');

1 row created.

SQL> INSERT INTO testb VALUES(6,'f');

1 row created.

SQL> COMMIT;

Commit complete.

用左连接,结果testb表里TempColum的值为NULL:

SQL> SELECT a.*,b.id "TempColum" FROM testa a LEFT JOIN testb b ON a.id=b.id;

        ID   VALUE       TempColum
---------- ----------       ----------
         2       b                   2
         4       d                   4
         6       f                    6
         5       e                   NULL 
         3       c                   NULL 
         1       a                   NULL 

6 rows selected.

将NULL值过滤出来就是最后需要的结果:

SQL>

SELECT c.id,c.value FROM
(
 SELECT a.*,b.id "TempColum" FROM testa a LEFT JOIN testb b ON a.id=b.id
) c
WHERE c."TempColum" IS NULL
ORDER BY c.id

SQL>
        ID VALUE
---------- ----------
         1 a
         3 c
         5 e

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16400082/viewspace-692708/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    165265