Null query result cant fetch in Select query in Oracle

Question

I have below query which i am trying to run but not returning the expected result. The ISIN field value which is Null in EXPORT_BB is also getting ignore and not showing the result with the condition given in NOT IN clause. The export_blacklist has only one row value and which is not Null but still i dont for what reason the null value is getting ignored.

Select * from EXPORT_BB where ISIN NOT IN
    (
        SELECT
            ISIN
        FROM
            export_blacklist);

If i run only select query without the NOT IN clause then i can see values which is NULL for ISIN field.

JUst for test i tried below query and its also resulting nothing. Is it bug in Oracle 18c or something is missing?

select 'null is not in set' from dual where null not in (select 1 from dual);

You need to say where isin is null or isin not in... null is neither in nor not in any result set... — sgeddes
– sgeddes, Commented Aug 29, 2019 at 13:13

forpas · Accepted Answer · 2019-08-29 13:15:43Z

3

Any comparison of NULL with =, <>, <, > or in a IN or NOT IN clause will return NULL, so that row is not included in the results (because only rows for which the returned value is TRUE will be included in the results).
Change your code with a condition for the case that ISIN is NULL:

SELECT * FROM EXPORT_BB 
WHERE ISIN NOT IN (SELECT ISIN FROM export_blacklist)
OR ISIN IS NULL

answered Aug 29, 2019 at 13:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

btpys Over a year ago

I guess that if there's a NULL in EXPORT_BB and there isn't in EXPORT_BLACKLIST, the OP won't want that row...

forpas Over a year ago

@btpys read the question again: The ISIN field value which is Null in EXPORT_BB is also getting ignore and not showing the result with the condition given in NOT IN clause. the OP wants to see the NULLs

btpys Over a year ago

sry, u're right, I mean't if there's also NULL in EXPORT_BLACKLIST. So if there are NULL in both tables, I guess the OP won't want to see it.

forpas Over a year ago

The export_blacklist has only one row value and which is not Null from the question

btpys Over a year ago

Yep, that's in the question, but I go further, the OP is treating NULLs as regular values, so I was guessing that if there are NULLs in both tables, he would't want it.

btpys · Accepted Answer · 2019-08-29 13:12:19Z

1

NULL values doesn't work with NOT IN it's the normal behaviour.

You have to convert the NULL to another value to be able to operate with it or use IS NULL/IS NOT NULL

Select * from EXPORT_BB where NVL(ISIN, 999999) NOT IN
    (
        SELECT
            NVL(ISIN, 999999)
        FROM
            export_blacklist);

answered Aug 29, 2019 at 13:12

btpys

3661 silver badge6 bronze badges

Comments

Josh Eller · Accepted Answer · 2019-08-29 13:12:56Z

1

Comparing to a null value in Oracle always returns false.

Is NULL >= 1? No.

Is NULL < 1? No.

Is NULL in your set? Regardless of what your set is, the answer is no.

Is NULL not in your set? Again, no.

answered Aug 29, 2019 at 13:12

Josh Eller

2,0859 silver badges11 bronze badges

2 Comments

Gordon Linoff Over a year ago

. . Technically, it returns NULL which WHERE clauses treat as false.

George Joseph Over a year ago

NULLS would be considered in the case of a NOT EXISTS scenario

George Joseph · Accepted Answer · 2019-08-29 13:15:32Z

1

It is the expected behaviour. NOt related to 18c it is the same way from Oracle 7 onwards

NOT IN doesnt consider nulls.

NOT EXISTS does consider nulls.

Consider the following example in db fiddle https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8be0a790d8172093a032602345038e8e

See a discussion on this https://asktom.oracle.com/pls/apex/asktom.search?tag=in-vs-exists-and-not-in-vs-not-exists

answered Aug 29, 2019 at 13:15

George Joseph

5,93213 silver badges25 bronze badges

Comments

tranesend · Accepted Answer · 2019-08-29 13:22:21Z

1

As you have been answered by collegues you have to specify that you wanna return null values too. Namely

    SELECT * 
    FROM EXPORT_BB 
    WHERE ISIN NOT IN (SELECT ISIN FROM EXPORT_BLACKLIST) 
    OR ISIN IS NULL;

answered Aug 29, 2019 at 13:22

tranesend

476 bronze badges

3 Comments

forpas Over a year ago

Did you just copy the code from my answer and posted it as your code?

tranesend Over a year ago

Nope. eheh it is very simple code for Oracle users, so it is normal codes are similar or look the same ;) It's like If I say you that you copy my code ideas if you write "SELECT SYSDATE FROM DUAL'

forpas Over a year ago

Really? And why did you post it? Was it missing from this thread?

Collectives™ on Stack Overflow

Null query result cant fetch in Select query in Oracle

5 Answers 5

5 Comments

Comments

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related