Re: understanding NOT IN / IN

From: ddf <oratune_at_msn.com>
Date: Tue, 17 Feb 2009 11:30:20 -0800 (PST)
Message-ID: <ba6f788a-0d1f-4dc8-90d1-cab77ef26559_at_f24g2000vbf.googlegroups.com>

On Feb 17, 1:27 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

And I shall stand corrected:

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

                              *

ERROR at line 2:
ORA-00904: "B"."ORG_ID": invalid identifier

SQL> -- returns 0 rows
SQL>
SQL> select * from a

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

                          *

ERROR at line 2:
ORA-00904: "B"."ORG_ID": invalid identifier

SQL> -- returns all 3 rows from a
SQL> I missed the 'b' in your statement, and read 'a'.

David Fitzjarrell Received on Tue Feb 17 2009 - 13:30:20 CST