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