​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Three different ways to write a “NOT IN” type query

 

We illustrate by using the popular emp and dept tables from the SCOTT schema

 

SQL> select * from scott.dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

SQL> select empno,ename,deptno from scott.emp

  2  /

 

     EMPNO ENAME          DEPTNO

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

      7369 SMITH              20

      7499 ALLEN              30

      7521 WARD               30

      7566 JONES              20

      7654 MARTIN             30

      7698 BLAKE              30

      7782 CLARK              10

      7788 SCOTT              20

      7839 KING               10

      7844 TURNER             30

      7876 ADAMS              20

 

     EMPNO ENAME          DEPTNO

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

      7900 JAMES              30  1 

 

Method 1


select * from scott.dept d

  2  where not exists

  3  (

  4* select null from scott.emp e where e.deptno = d.deptno)

SQL> /

 

    DEPTNO DNAME          LOC

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

        40 OPERATIONS     BOSTON


  

 1   select * from scott.dept

  2   where deptno not in

  3   (

  4*  Selectdeptno from scott.emp)

SQL> /

 

    DEPTNO DNAME          LOC

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

        40 OPERATIONS     BOSTON

 

  1  select e1.deptno,e1.dname,e1.loc

  2  fromscott.dept e1 ,scott.emp e2

  3  where e1.deptno = e2.deptno(+)

  4* and e2.rowid is null

SQL> /

 

    DEPTNO DNAME          LOC

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

        40 OPERATIONS     BOSTON

 

 

Which one is the quickest.?   Answer, …………  it depends.

 

I use method 3 a lot and method 2 can be surprisingly quick  too but obviously you need to test !