Re: SQL Query Help please

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 18 Oct 1998 01:37:07 GMT
Message-ID: <362a45b7.6091449@192.86.155.100>

A copy of this was sent to "MUJAHID HAMID" <mujahid_at_pharmco.demon.co.uk> (if that email address didn't require changing) On Sat, 17 Oct 1998 17:37:33 +0100, you wrote:

>Dear All,
>
>I have the following select:
>
>Select account_no from table1
>where account_no not in (select account_no from table2).
>
>I am trying to say that give me the account_no's in table1 that are not in
>table2.
>
>In the result I do not get any rows back and I know for a fact that there
>are rows in table1 that are not in table2.
>(Table2 has more rows than table1)
>
>Thanks in advance
>mujahid_at_pharmco.demon.co.uk
>

Beware the occasional NULL value! Consider:

SQL> create table t1 ( account_no int ); Table created.

SQL> create table t2 ( account_no int ); Table created.

SQL> begin

  2          for i in 1..10 loop
  3                          insert into t1 values ( i );
  4          end loop;

  5 end;
  6 /
PL/SQL procedure successfully completed.

SQL> insert into t2 values ( 1 );
SQL> insert into t2 values ( NULL );

SQL> select account_no from t1
  2 where account_no not in (select account_no from t2 );

no rows selected

SQL> select account_no from t1
  2 where NOT EXISTS ( select null from t2 where t2.account_no = t1.account_no);

ACCOUNT_NO

---

         2
         3
         4
         5
         6
         7
         8
         9
        10

9 rows selected.

SQL> select account_no from t1
  2 minus
  3 select account_no from t2;

ACCOUNT_NO

---

         2
         3
         4
         5
         6
         7
         8
         9
        10

9 rows selected.

See, not in ( ..., NULL, ... ) is always "unknown", eg:

SQL> select * from dual where dummy not in ( NULL );

no rows selected

SQL> c/not in/in/
  1* select * from dual where dummy in ( NULL ) SQL> / no rows selected

SQL> select * from dual;

D
-
X

I suggest using NOT EXISTS ....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Oct 17 1998 - 20:37:07 CDT