Re: understanding NOT IN / IN
Date: Tue, 17 Feb 2009 11:20:27 -0800 (PST)
Message-ID: <4ce45a13-fc11-4d22-a464-c90df74842c6_at_s20g2000yqh.googlegroups.com>
On Feb 17, 12:41 pm, 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
Running your queries with autotrace on tells the tale:
SQL> select * from a
2 where a.org_id not in (select 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))
The LNNVL function provides a way to evaluate a condition when one or both operands of the condition may be null. Oracle will occasionally uses the LNNVL function internally to rewrite NOT IN conditions as NOT EXISTS conditions, as it did here. It returns, essentially, the opposite of the actual 'truth' of the statement: conditions which evaluate to TRUE return FALSE with LNNVL, and conditions which evaluate to FALSE or UNKNOWN return TRUE. The LNNVL function is available for use only in WHERE clauses. With that in mind the :B1 <> :B2 condition is UNKNOWN, LNNVL returns TRUE, and 0 exists for each row in table a, causing the NOT EXISTS condition to be FALSE, returning no rows. (I suspect that :B1 is set to the org_id in the currently accessed row, and :B2 is set to NULL as there is no org_id in that table.)
Your second query produces the following plan from autotrace:
SQL> select * from a
2 where a.org_id in (select 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 the query was rewritten as an EXISTS query, and that :B1 and :B2 were chosen to produce a TRUE result (they're equal and non-null) so that every pair of values will cause the select to return 0, thus the EXISTS condition evaluates to TRUE and returns all of the data from table a even though there is no org_id in table b. (I suspect :B1 and :B2 are set to the org_id value from each row in table a as it's accessed.)
David Fitzjarrell Received on Tue Feb 17 2009 - 13:20:27 CST