Re: Why doesnt this query work?? Makes no sense????

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/03
Message-ID: <8jqtgh$3nm$1@nnrp1.deja.com>#1/1

In article <8jqrmg$2dm$1_at_nnrp1.deja.com>,   ddf_dba_at_my-deja.com wrote:
> In article <8jqlfb$tgk$1_at_nnrp1.deja.com>,
> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:

[snip]

> > > > > select patientid
> > > > > from B
> > > > > where patientid not in (select patientid from A);
> > > > >
> >
> > [snip]
> >
> > > >
> > > > you have a NULL patientid in table A. When you say "where x not

 in


> >     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

> >

> > [snip]

> >

> > >
> > > I don't know which version of Oracle you are running, Tom, but I
 cannot
> > > get Oracle to exhibit the same behavior on 8.1.5:
> > >
> >
> > you put the NULL row into the wrong table. Put a NULL row into
 tableA,
> > not table B. Its the thing in the NOT IN ( .... ) that is the
 cause.
>
> With a primary key on table A a NULL value CANNOT be inserted.

I know that -- go back to the original question:

select patientid
  from B
    where patientid not in (select patientid from A);

That was the query -- you added a primary key to the wrong table for the example, you reversed the tables ( the table in the SUBQUERY needs to have a NULL in it -- I said that clearly) and that is why you could not get the answer I got. You had B where they had A and A where they had B.

if you go back to
http://www.deja.com/=gh/threadmsg_md.xp?AN=639788137&fmt=text

you'll see I have a t1 (table B) and t2 (table A) and they are defined as:

ops$tkyte_at_8i> create table t1 ( x int, constraint t1_pk primary key(x)); Table created.

ops$tkyte_at_8i> create table t2 ( x int, constraint t2_unique unique(x) ); Table created.

in my example. and then went on:

ops$tkyte_at_8i> select * from t1 where x not in ( select x from t2 );

         X

---

         2

There we get what we expect but as soon as we stick a single NULL in there:

ops$tkyte_at_8i> insert into t2 values ( NULL ); 1 row created.

ops$tkyte_at_8i> select * from t1 where x not in ( select x from t2 ); no rows selected

That query will return 0 rows for ever.

Showing you need to add the NULL to the table in the subquery -- not in the table you are selecting from.

>


> >

> > > SQL> create table a(patientid int, constraint a_pk primary

> > > key(patientid));

> > >

> > > Table created.

> > >
> > > SQL> create table b(patientid int, constraint b_uq
 unique(patientid));
> > >
> > > Table created.
> > >
> > > Using SQL*Loader I populated both tables a and b; the results of
 the
> > > data loads are shown below:
> > >
> > [snip]
> >
> > >
> > > Now I add a NULL to table b:
> > >
> >
> > thats the mistake.
> >

>
> No, it isn't a mistake, since a NULL is not allowed in table A
 (primary
> key) -- please read the 'create table' statements again. However,
> reversing the data sets DID produce the results you mentioned.
>

I understand you cannot insert a NULL into your table A -- and thats the problem. You've gotten them backwards.

you have to reverse YOUR datasets to answer both the question and reproduce my example

[snip]

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Jul 03 2000 - 00:00:00 CDT