understanding NOT IN / IN

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 Received on Tue Feb 17 2009 - 12:41:23 CST