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 -
Plan hash value: 1043214102
SQL>
SQL> select * from a
2 where a.org_id in (select a.org_id from b);
Plan hash value: 1043214102
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