create table tq84_A (id number not null);
create table tq84_B (id number not null);
explain plan for
select * from tq84_A
where
id not in (select id from tq84_B);
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN ANTI | |
-- | 2 | TABLE ACCESS FULL| TQ84_A |
-- | 3 | TABLE ACCESS FULL| TQ84_B |
-- -------------------------------------
If the table tq84_B is created with id as nullable, the optimizer chooses HASH JOIN ANTI NA (rather than HASH JOIN ANTI NA).