Re: IN ignore null values, my querys really prove that point!!

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 20 Feb 2004 14:58:25 -0400
Message-ID: <001f01c3f7e3$8576ad10$2501a8c0@dazasoftware.com>

Thomas you are right, I didn't said the contrary,  that is the point I showed in a query, that situation, because some times one forget it, when analyzing if use IN or EXISTS

To show the situation that any column against a value of NULL *never* return a true condition using IN I did that examples.

 Maybe you could prefer an example like this  select count(*) from dual where null in (null );

 COUNT(*)

---

         0
 :)
>
>
> Message -----
> From: "Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us>
> To: <oracle-l_at_freelists.org>
> Cc: <jreyes_at_dazasoftware.com>
> Sent: Friday, February 20, 2004 10:47 AM
> Subject: RE: IN ignore null values
>
>
> > Juan,
> >
> > Your queries really don't prove anything.
> >
> > select null from daz.utl_tablas_me works the same as
> > select 42 from daz.utl_tablas_me
> >
> > Your use of the IN clause versus the EXISTS clause are different. The
IN
> > clause is comparing the tsm_dsm column against a value of NULL which
> should
> > *never* return a true condition.
> >
> > Your use of EXISTS says to return a TRUE value from the subquery if the
> > query is valid. Performing a select null from table *always* returns
a
> > true result because you are not using a where clause.
> >
> > Hope this helps.
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> > -----Original Message-----
> > From: Juan Cachito Reyes Pacheco [mailto:jreyes_at_dazasoftware.com]
> > Sent: Friday, February 20, 2004 9:33 AM
> > To: antonio; carlos; daniel; danita; ivan; jaime; javier; juan; luis;
> > oracle-l_at_freelists.org; richi
> > Subject: IN ignore null values
> >
> >
> > An interesting observation
> >
> > IN ignore null values as you can see in the table there are 310 null
> values
> > in column TBL_DSM.
> >
> > SQL> select count(*) from daz.utl_tablas_me
> >
> > 2 where tbl_dsm in
> >
> > 3 (select null from daz.utl_tablas_me);
> >
> > COUNT(*)
> >
> > ---------
> >
> > 0
> >
> > SQL> select count(*) from daz.utl_tablas_me
> >
> > 2 where exists(select null from daz.utl_tablas_me);
> >
> > COUNT(*)
> >
> > ---------
> >
> > 310
> >
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 17:58:25 CST