RE: Difference between NOT IN and MINUS

From: Figueiredo, Pedro <PFigueiredo_at_europeantelecom.at>
Date: Sat, 2 Sep 2000 00:37:56 -0000
Message-Id: <10606.116107@fatcity.com>

Hi,

You have null values on table_object.asset_id. When using MINUS, the query is not "sensible" to this null values. With NOT IN, a false condition is generated thus, bringing no rows.

Try this one:
select asset_id from easset where asset_id not in (select asset_id from table_object where asset_id is not null);

... the result will be different.

Still, the use of NOT IN is not advisable unless the table is very small. Performance is very poor with large tables.

Hope this helps. Bye.

Pedro Figueiredo

-----Original Message-----
From: yong huang [mailto:yong321_at_yahoo.com] Sent: sexta-feira, 1 de Setembro de 2000 19:41 To: Multiple recipients of list ORACLE-L Subject: Difference between NOT IN and MINUS

Can anyone help me understand this? Shouldn't the queries below using NOT IN return more rows than or the same number of rows as the queries using MINUS? (In case of "more", it will contain duplicates).

Yong Huang
yong321_at_yahoo.com

SQL> select asset_id from easset where asset_id not in (select asset_id from table_object);

no rows selected

SQL> select asset_id from easset minus select asset_id from table_object;

ASSET_ID

10 rows selected.

--Create a table qqq in case Oracle doesn't like the word "table_object" SQL> create table qqq as select * from table_object;

Table created.

SQL> select asset_id from easset where asset_id not in (select asset_id from qqq);

no rows selected

SQL> desc easset

 Name                            Null?    Type


 ------------------------------- -------- ----

 ASSET_ID                        NOT NULL NUMBER
 COMPANY_ID                      NOT NULL NUMBER
 PACKAGE_ID                      NOT NULL NUMBER
 ASSET_NAME                      NOT NULL VARCHAR2(100)
 OPERATING_COMPANY_NAME                   VARCHAR2(100)
 ONSHORE_OFFSHORE_TRANS_IND               VARCHAR2(20)
 ASSET_PRICE_RANGE_USD                    VARCHAR2(20)
 CREATED_DATE                    NOT NULL DATE
 CREATED_BY_USER_ID              NOT NULL NUMBER
 LAST_REVISION_DATE                       DATE
 LAST_REVISED_BY_USER_ID                  NUMBER
 MAP_OBJECT_ID                            VARCHAR2(30)
 NOTIFICATION                             NUMBER
 UPPER_RIGHT_X                            NUMBER
 UPPER_RIGHT_Y                            NUMBER
 LOWER_LEFT_X                             NUMBER
 LOWER_LEFT_Y                             NUMBER
 LOCATION_ID                              NUMBER
 COUNTRY_ID                               NUMBER
 APPROX_NET_RESERVES_BOE                  VARCHAR2(20)
 APPROX_NET_RESERVES_MCFE                 VARCHAR2(20)
 DATA_BOOK_AVAILABLE_FLAG                 VARCHAR2(1)

SQL> desc table_object
 Name                            Null?    Type


 ------------------------------- -------- ----

 OBJECT_TYPE_ID                  NOT NULL NUMBER
 OBJECT_ID                       NOT NULL NUMBER
 IUSER_ID                                 NUMBER
 COMPANY_ID                               NUMBER
 PACKAGE_ID                               NUMBER
 ASSET_ID                                 NUMBER
 LEASE_LICENSE_ID                         NUMBER
 ESEISMIC_ID                              NUMBER

SQL> select package_id from ip1.text minus select package_id from table_object;

PACKAGE_ID

8 rows selected.

SQL> select package_id from ip1.text where package_id not in (select package_id
from table_object);

no rows selected

SQL> desc ip1.text

 Name                            Null?    Type


 ------------------------------- -------- ----

 TEXT_ID                         NOT NULL NUMBER
 SEQUENCE_NO                     NOT NULL NUMBER
 SECTION_ID                               NUMBER
 PACKAGE_ID                               NUMBER
 TEXT_FIELD                      NOT NULL VARCHAR2(4000)
 ASSET_ID                                 NUMBER
 ESEISMIC_ID                              NUMBER

__________________________________________________

Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/

-- 
Author: yong huang
  INET: yong321_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Fri Sep 01 2000 - 19:37:56 CDT