Re: update based on query results question

From: AK <steven.kuzine_at_gmail.com>
Date: 5 Jul 2005 10:34:47 -0700
Message-ID: <1120584887.486168.55610@z14g2000cwz.googlegroups.com>

maybe it's all about columna being nullable, that's a very common mistake:

create table a(m number, n number)
Table created

insert into a values(1,1)
1 row inserted

insert into a values(2,null)
1 row inserted

insert into a values(3,3)
1 row inserted

create table b(n number)
Table created

insert into b values(1)
1 row inserted

insert into b values(null)
1 row inserted

select * from a

         M N
---------- ----------

         1          1
         2
         3          3

3 rows selected

select * from b

         N

---

         1

2 rows selected

• wrong select * from a where n in(select n from b) M N
  ---------- ----------
  1 1 1 row selected
  • the right way select * from a where exists(select 1 from b where a.n=b.n or (a.n is null and b.n is null)) M N
    ---------- ----------
    1 1 2 2 rows selected

drop table a
Table dropped  

drop table b
Table dropped Received on Tue Jul 05 2005 - 12:34:47 CDT