NOT IN Operator not working as expected in Oracle

Question

I have two tables: PROD and CUST On running the below query in SQL and SYBASE, it works. Oracle is not giving any results.

select * FROM PROD
where PROD.SECID NOT IN (SELECT CUST.SECID FROM CUST WHERE SECID <> '')

NOTE: PROD.SECID has all null values. CUST.SECID has all non null values(valid values).

<>'' doesn't fetch any records from inner subquery so i changed it to IS NOT NULL and now it fetches the results.

But the problem is that when query is run as a whole, it doesn't give any results whereas it should.

Answer 1

NOT IN works as it should. If you have null value you cannot say is it 'in' given set or no.

SQL> select case
  2         when null in (1, 2, 3) then 'in'
  3         when null not in (1, 2, 3) then 'not in'
  4         else 'null'
  5         end as result
  6  from dual;

RESULT
------
null     

If you wish to exclude given values you should use not exists, or, better, anti-join:

select p.*
from prod p, cust c
where p.secid = c.secid (+)
and c.secid is null;