Wednesday, 2 January 2013

What is the difference between NOT IN and MINUS


supose you have 2 tables
T1 (code varchar2(5));
T2 (code varchar2(5));

CREATE TABLE T1 (code varchar2(5));
INSERT INTO T1 VALUES('A1');
INSERT INTO T1 VALUES('A2');
INSERT INTO T1 VALUES('A3');
INSERT INTO T1 VALUES('A4');
INSERT INTO T1 VALUES(NULL);

CREATE TABLE T2 (code varchar2(5));
INSERT INTO T2 VALUES('A1');
INSERT INTO T2 VALUES('A2');
INSERT INTO T2 VALUES('B1');
INSERT INTO T2 VALUES(NULL);
INSERT INTO T2 VALUES(NULL);
COMMIT;
/

i made this 2 select :

SQL> select code from T1
     where code not in (select distinct code from t2);

no rows selected

SQL> select code  from T1
    minus
    select code from t2;
    
CODE
----
A3
A4


Case #1
code in t2  has some nulls, it is not KNOWN the values contains nulls.
"NOT IN" is sensible to null values, so a false condition is generated thus, bringing no rows.
Note: Still, the use of NOT IN is not advisable unless the table is very small. Performance is very poor with large tables.

select * from dual where 1 not in ( 2, null );
no rows selected

Case #2

When using MINUS, the query is not "sensible" to this null values. so it is fetching distinct values.

Practice Below:
-----------------

delete from T1 where code is null;

Case1#
----------
select * from t2 where code not in (select * from T1)

CODE
-----
B1

Case2#
--------------
select * from t2
minus
select * from T1

CODE
----
B1
NULL