12 Replies Latest reply: Jul 20, 2012 1:29 AM by loloFromStAlbanDeRoche RSS

    Frequent questions: NOT IN vs  NOT EXISTS

    797013
      Hi all,

      Still i am not getting clear picture of difference between NOT IN and not exists. Can any one explain me with example. (Pls dont give any link for the reference)

      Whenever i google it for this i get an example as below
      with t as 
      ( select 1 empid,'arumug' name,'manager' designation,1 as mgr_id from dual
        union all
        select 2 empid,'dinesh' name,'SSE' designation, 2 as mgr_id from dual
        union all 
        select 3 empid,'SEN' name,'SSE' designation, null as mgr_id from dual
        union all
        select 4 empid,'SAM' name,'SSE' designation, null as mgr_id from dual
        )
      select * from t where t.empid not in ( select mgr_id from t)
      Yes i did not return any values because of null values. this can be corrected as
      select  * from t  where not exists ( select mgr_id from t t1 where t1.empid=t.mgr_id)
      i can get now 2 rows.

      but my doubt is the query
      select * from t where t.empid not in ( select mgr_id from t t1 where t1.empid=t.mgr_id)
      also gives the same output as not exists.

      Can anyone explain this with example..

      Pls explain

      waiting

      S

      Edited by: oraclehema on Jul 18, 2012 11:48 PM
        • 1. Re: Frequent questions: NOT IN vs  NOT EXISTS
          HuaMin Chen
          Well, the two are processed very very differently.

          Select * from T1 where x in ( select y from T2 )

          is typically processed as:

          select *
          from t1, ( select distinct y from t2 ) t2
          where t1.x = t2.y;

          The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
          the original table -- typically.


          As opposed to

          select * from t1 where exists ( select null from t2 where y = x )

          That is processed more like:


          for x in ( select * from t1 )
          loop
          if ( exists ( select null from t2 where y = x.x )
          then
          OUTPUT THE RECORD
          end if
          end loop

          It always results in a full scan of T1 whereas the first query can make use of an index
          on T1(x).


          So, when is where exists appropriate and in appropriate?

          Lets say the result of the subquery
          ( select y from T2 )

          is "huge" and takes a long time. But the table T1 is relatively small and executing (
          select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the
          exists will be faster as the time to full scan T1 and do the index probe into T2 could be
          less then the time to simply full scan T2 to build the subquery we need to distinct on.


          Lets say the result of the subquery is small -- then IN is typicaly more appropriate.


          If both the subquery and the outer table are huge -- either might work as well as the
          other -- depends on the indexes and other factors.
          • 2. Re: Frequent questions: NOT IN vs  NOT EXISTS
            Karthick_Arp
            Consider a simple example. This is my EMP Table
            SQL> select * from emp
              2  /
             
                 EMPNO     DEPTNO ENAME                       SAL DOJ       JOB               MGR
            ---------- ---------- -------------------- ---------- --------- ---------- ----------
                     1          1 Karthick                     80 03-DEC-11
                     2          1 Karthick_1                   90 23-NOV-11                     1
                     3          2 1                            80 03-DEC-11
                     4          2 Ram_1                        90 23-NOV-11                     3
            Now i need to find the list of employee who are not managers.

            *1. Using NOT EXISTS.*
            SQL> select * from emp e1 where not exists (select * from emp e2 where e1.empno = e2.mgr)
              2  /
             
                 EMPNO     DEPTNO ENAME                       SAL DOJ       JOB               MGR
            ---------- ---------- -------------------- ---------- --------- ---------- ----------
                     2          1 Karthick_1                   90 23-NOV-11                     1
                     4          2 Ram_1                        90 23-NOV-11                     3
            So perfect employee 2 and 4 are not managers.

            *2. Using NOT IN*
             
            SQL> select * from emp e1 where e1.empno not in (select e2.mgr from emp e2)
              2  /
             
            no rows selected
            So this says all the employee are managers, How?

            Thats because of the NULL in the result of the subquery used in NOT IN. Let me use NVL to eliminate the NULL values.
            SQL> select * from emp e1 where e1.empno not in (select nvl(e2.mgr, 0) from emp e2)
              2  /
             
                 EMPNO     DEPTNO ENAME                       SAL DOJ       JOB               MGR
            ---------- ---------- -------------------- ---------- --------- ---------- ----------
                     2          1 Karthick_1                   90 23-NOV-11                     1
                     4          2 Ram_1                        90 23-NOV-11                     3
             
            See now you get the values.
            • 3. Re: Frequent questions: NOT IN vs  NOT EXISTS
              797013
              Well said with example. Thanks.
              • 4. Re: Frequent questions: NOT IN vs  NOT EXISTS
                797013
                karthik,

                thanks for your example. still i will be happy if you explain this...
                select * from emp e1 where empno not in  (select mgr_id from emp e2 where e1.empno = e2.mgr)
                {code)
                
                also gives you the same result as not exists  right?  
                
                Pls clarify...
                
                S
                
                Edited by: oraclehema on Jul 19, 2012 12:21 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                • 5. Re: Frequent questions: NOT IN vs  NOT EXISTS
                  Karthick_Arp
                  oraclehema wrote:
                  karthik,

                  thanks for your example. still i will be happy if you explain this...
                  select * from emp e1 where empno not in  (select * from emp e2 where e1.empno = e2.mgr)
                  also gives you the same result as not exists right?

                  Pls clarify...

                  S
                  This code will only give you error
                  SQL> select * from emp e1 where empno not in (select * from emp e2 where e1.empno = e2.mgr)
                    2  /
                  select * from emp e1 where empno not in (select * from emp e2 where e1.empno = e2.mgr)
                                                           *
                  ERROR at line 1:
                  ORA-00913: too many values
                   
                   
                  SQL> 
                  • 6. Re: Frequent questions: NOT IN vs  NOT EXISTS
                    797013
                    sorry i edited.

                    select * from emp e1 where empno not in  (select  mgr_id from emp e2 where e1.empno = e2.mgr)
                    Using this NOT IN also gives you same result as NOT EXISTS.

                    but v go for NOT EXISTS because of performance issue as explained above ? any other functional reasons ?

                    S

                    Edited by: oraclehema on Jul 19, 2012 12:36 AM
                    • 7. Re: Frequent questions: NOT IN vs  NOT EXISTS
                      Hoek
                      oraclehema wrote:
                      Well said with example. Thanks.
                      You mean:
                      well copied without a reference to the original author.

                      See: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074

                      By the way, on recent DB-versions Optimizer may decide to rewrite IN into EXISTS and the opposite...

                      Edited by: Hoek on Jul 19, 2012 10:00 AM
                      • 8. Re: Frequent questions: NOT IN vs  NOT EXISTS
                        Karthick_Arp
                        In both the case oralce converts the NOT IN operator int NOT EXISTS and process it.

                        But just evaluates it differently.

                        In the first case it uses LNNVL and does the comparison as NOT EQUAL TO.
                        SQL> select * from emp e1 where e1.empno not in (select e2.mgr from emp e2)
                          2  /
                         
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 107605425
                         
                        ---------------------------------------------------------------------------
                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |      |     1 |    80 |     4   (0)| 00:00:01 |
                        |*  1 |  FILTER            |      |       |       |            |          |
                        |   2 |   TABLE ACCESS FULL| EMP  |     4 |   320 |     2   (0)| 00:00:01 |
                        |*  3 |   TABLE ACCESS FULL| EMP  |     4 |    52 |     2   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "E2" WHERE
                                      LNNVL("E2"."MGR"<>:B1)))
                           3 - filter(LNNVL("E2"."MGR"<>:B1))
                         
                        Note
                        -----
                           - dynamic sampling used for this statement
                        In the second case it is exactly evaluated as NOT EXISTS
                        SQL> select * from emp e1 where e1.empno not in (select e2.mgr from emp e2 where e2.mgr = e1.empno)
                          2  /
                         
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 107605425
                         
                        ---------------------------------------------------------------------------
                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |      |     1 |    80 |     4   (0)| 00:00:01 |
                        |*  1 |  FILTER            |      |       |       |            |          |
                        |   2 |   TABLE ACCESS FULL| EMP  |     4 |   320 |     2   (0)| 00:00:01 |
                        |*  3 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "E2" WHERE
                                      "E2"."MGR"=:B1))
                           3 - filter("E2"."MGR"=:B1)
                        And the NOT EXIST condition
                        SQL> select * from emp e1 where not exists (select e2.mgr from emp e2 where e1.empno = e2.mgr)
                          2  /
                         
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 107605425
                         
                        ---------------------------------------------------------------------------
                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |      |     1 |    80 |     4   (0)| 00:00:01 |
                        |*  1 |  FILTER            |      |       |       |            |          |
                        |   2 |   TABLE ACCESS FULL| EMP  |     4 |   320 |     2   (0)| 00:00:01 |
                        |*  3 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "E2" WHERE
                                      "E2"."MGR"=:B1))
                           3 - filter("E2"."MGR"=:B1)
                         
                        Note
                        -----
                           - dynamic sampling used for this statement
                         
                        So when you use correlated subquery both NOT IN and NOT EXISTS acts in the same way.

                        But even more fun part is when you use NOT IN with NOT NULL check Like this.
                        SQL> select * from emp e1 where e1.empno not in (select e2.mgr from emp e2 where e2.mgr is not null)
                          2  /
                         
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 2487432012
                         
                        ---------------------------------------------------------------------------------------
                        | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT             |        |     3 |   279 |     4  (25)| 00:00:01 |
                        |   1 |  MERGE JOIN ANTI             |        |     3 |   279 |     4  (25)| 00:00:01 |
                        |   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     4 |   320 |     1   (0)| 00:00:01 |
                        |   3 |    INDEX FULL SCAN           | EMP_PK |     4 |       |     1   (0)| 00:00:01 |
                        |*  4 |   SORT UNIQUE                |        |     2 |    26 |     3  (34)| 00:00:01 |
                        |*  5 |    TABLE ACCESS FULL         | EMP    |     2 |    26 |     2   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------------------
                         
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                         
                           4 - access("E1"."EMPNO"="E2"."MGR")
                               filter("E1"."EMPNO"="E2"."MGR")
                           5 - filter("E2"."MGR" IS NOT NULL)
                         
                        Note
                        -----
                           - dynamic sampling used for this statement
                        So its better to use a NOT NULL check to make the benefit out of an NOT IN operator.
                        • 9. Re: Frequent questions: NOT IN vs  NOT EXISTS
                          797013
                          ok.then. thanks.

                          From your point i conclude NOT IN & NOT EXISTS ( for the scnearios explained above) will work same.

                          Thanks for explanation

                          S
                          • 10. Re: Frequent questions: NOT IN vs  NOT EXISTS
                            Hoek
                            "if you always select NOT NULLABLE expressions - the two (not in/not exists) are interchangable as above."
                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074#2665756700346116595
                            • 11. Re: Frequent questions: NOT IN vs  NOT EXISTS
                              rp0428
                              >
                              From your point i conclude NOT IN & NOT EXISTS ( for the scnearios explained above) will work same.
                              >
                              Note that the plan hash value for the first three examples are the same
                              >
                              Plan hash value: 107605425
                              >
                              This means it is the same query! Even though the filter predicates for example 1 and 2/3 are different.

                              Also note that example 2 and 3 are absolutely identical even though one query uses not in and the other uses not exists.