2

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.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
user3346282
  • 33
  • 1
  • 10
  • "*On running the below query in SQL*" doesn't make sense. The query **is** SQL and Oracle **is** using SQL. Are you aware that Oracle has not "empty strings"? – a_horse_with_no_name Apr 20 '15 at 10:18
  • 1
    Be sure to give Rahul the credit (reputation points) he deserves. Click on the check mark by the top left of his answer to "accept" it as the correct answer to your question. Good luck to all. – shellter Apr 20 '15 at 14:10

3 Answers3

4

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;
Sanders the Softwarer
  • 2,418
  • 1
  • 11
  • 28
1

Try this:

select PROD.* FROM PROD LEFT JOIN CUST 
ON  PROD.SECID = CUST.SECID
Rahul Tripathi
  • 152,732
  • 28
  • 233
  • 299
1

I've noticed 2 things:

1) Not is return FALSE if there is at least one NULL value in list. So if you subquery return NULLs then overall query will return 0 rows.

2) There is invalid filter in your subquery: 

WHERE SECID <> ''

This clause will return 0 rows as well as '' is the same as NULL and any comparison with NULL (except IS NULL/IS NOT NULL) returns FALSE. Please rewrite it as following:

WHERE SECID IS NOT NULL
Rusty
  • 1,889
  • 8
  • 12
  • NOT is NOT return false :) It returns null in this case. – Sanders the Softwarer Apr 20 '15 at 16:01
  • It returns NULL in SQL because BOOLEAN is not supported in SQL. Bit it does not matter. The fact is operand <> '' does not work. I think it is more important understand this fact. – Rusty Apr 21 '15 at 08:46
  • It returns null independently of boolean support. It's valuable in some cases, for example in check constraints. About <> '' author wrote almost off the bat, in minutes after first post, so he's understood for sure. – Sanders the Softwarer Apr 21 '15 at 12:06