Re: IN ignore null values
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 20 Feb 2004 15:21:58 -0800
It works exactly as I expected.
It proves that null is never equal to an arbitrary column, and that
there is 100 rows in x.
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> drop table x;
Table dropped.
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> create table x
15:20:50 2 as
15:20:50 3 select owner, table_name, tablespace_name
15:20:50 4 from dba_tables
15:20:50 5 where rownum <= 100
15:20:50 6 /
Table created.
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> select count(*)
15:20:50 2 from x
15:20:50 3 where table_name in (
15:20:50 4 select null from x
15:20:50 5 )
15:20:50 6 /
COUNT(*)
----------
0
1 row selected.
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL> select count(*)
15:20:50 2 from x
15:20:50 3 where exists ( select null from x )
15:20:50 4 /
COUNT(*)
----------
100
1 row selected.
15:20:50 rsysdevdb.radisys.com - jkstill@dv01 SQL>
"Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/20/2004 10:48 AM
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
cc:
Subject: Re: IN ignore null values
1. I don't have only 310 rows, why don't you test.
2. here is an example of the effect of using IN() in a query,
as consequence of the inequality NULL=NULL
I don't understand what is not clear here, you can test it by yourself.
:)
----- Original Message -----
From: <Jared.Still@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, February 20, 2004 1:18 PM
Subject: Re: IN ignore null values
Actually, all this proves is that you have 310 rows in the table
utl_tablas_me.
Jared
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
