Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
4186 4843 5050 5180 5209 5212 5240 5242 5243 5253
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
524 583 623 645 648 651 683 684
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? TypeReceived on Fri Sep 01 2000 - 11:38:38 CDT
------------------------------- -------- ----
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 __________________________________________________
![]() |
![]() |