Re: understanding NOT IN / IN

From: ddf <oratune_at_msn.com>
Date: Tue, 17 Feb 2009 11:27:50 -0800 (PST)
Message-ID: <c25b6141-d36f-49ba-b311-eed5ab1229d1_at_w34g2000yqm.googlegroups.com>



On Feb 17, 1:21 pm, rgvguplb <rgvgu..._at_gmail.com> wrote:
> On Feb 17, 10:41 am, ciapecki <ciape..._at_gmail.com> wrote:
>
>
>
>
>
> > Following situation:
>
> > create table a (org_id number, org_name varchar2(1));
> > insert into a values(1,'a');
> > insert into a values(2,'b');
> > insert into a values(3,'c');
>
> > create table b (name varchar2(1));
> > insert into b values('a');
> > insert into b values('b');
>
> > create table c (org_id number, org_name varchar2(1));
> > insert into c values(2,'b');
> > commit;
>
> > select * from a
> > where a.org_id not in (select org_id from b);
> > -- returns 0 rows
>
> > select * from a
> > where a.org_id in (select org_id from b);
> > -- returns all 3 rows from a
>
> > Why does it actually work?
>
> > there is no org_id in table b.
> > when you run
> > select org_id from b
> >         *
> > ORA-00904: "ORG_ID": invalid identifier
>
> > thanks,
> > chris
>
> i'm guessing the org_id in your subquery refers to org_id in the outer
> query. stick a b. before it and it won't run.
> you may not want it that way, but that's probably what it's doing.- Hide quoted text -
>
> - Show quoted text -

I hate to burst your bubble but doing as you suggest doesn't affect the queries or the plans in the slightest:

SQL> select * from a
  2 where a.org_id not in (select a.org_id from b);

no rows selected

Execution Plan



Plan hash value: 1043214102
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    15 |     6   (0)|
00:00:01 |
|*  1 |  FILTER             |      |       |       |
|          |
|   2 |   TABLE ACCESS FULL | A    |     3 |    45 |     3   (0)|
00:00:01 |
|*  3 |   FILTER            |      |       |       |
|          |
|   4 |    TABLE ACCESS FULL| B    |     2 |       |     3   (0)|
00:00:01 |

Predicate Information (identified by operation id):


   1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE LNNVL (:B1<>:B2)))

   3 - filter(LNNVL(:B1<>:B2))

Note


  • dynamic sampling used for this statement

Statistics


         13  recursive calls
          0  db block gets
         48  consistent gets
          0  physical reads
          0  redo size
        340  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> -- returns 0 rows

SQL>
SQL> select * from a
  2 where a.org_id in (select a.org_id from b);

    ORG_ID O
---------- -

         1 a
         2 b
         3 c


Execution Plan



Plan hash value: 1043214102
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)|
Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    45 |     6   (0)|
00:00:01 |
|*  1 |  FILTER             |      |       |       |
|          |
|   2 |   TABLE ACCESS FULL | A    |     3 |    45 |     3   (0)|
00:00:01 |
|*  3 |   FILTER            |      |       |       |
|          |
|   4 |    TABLE ACCESS FULL| B    |     2 |       |     3   (0)|
00:00:01 |

Predicate Information (identified by operation id):


   1 - filter( EXISTS (SELECT 0 FROM "B" "B" WHERE :B1=:B2))    3 - filter(:B1=:B2)

Note


  • dynamic sampling used for this statement

Statistics


         13  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> -- returns all 3 rows from a
SQL> David Fitzjarrell Received on Tue Feb 17 2009 - 13:27:50 CST

Original text of this message