CODEWITHSUNDEEP
oracle-database
JokingBatman
JokingBatman

Reputation: 66

What is the difference between "IS NOT NULL" and "NOT IN NULL"? Why these two Oracle queries return different results?

  1. SELECT DUMMY FROM DUAL WHERE DUMMY IS NOT NULL;

    Result: Returns 'X';

  2. SELECT DUMMY FROM DUAL WHERE DUMMY NOT IN NULL;

    Result: Returns nothing.

Upvotes: 0

Views: 55

Answers (2)

MT0
MT0

Reputation: 168361

In Oracle binary expressions can evaluate to three states: TRUE, FALSE and NULL where NULL can be thought of as the same as a undefined or unknown value. Applying a binary operation to a NULL value will give a NULL output (and not, as you might expect, TRUE or FALSE):

Boolean Operation Result
----------------- ------
X = X             TRUE
X = Y             FALSE
X = NULL          NULL
NULL = X          NULL
NULL = NULL       NULL
NULL IN ( NULL )  NULL
NOT TRUE          FALSE
NOT FALSE         TRUE
NOT NULL          NULL

The query:

SELECT DUMMY FROM DUAL WHERE DUMMY NOT IN ( NULL )

is the equivalent of:

SELECT DUMMY FROM DUAL WHERE NOT( DUMMY = NULL )

And the WHERE clause evaluates NOT( DUMMY = NULL ) to NOT NULL which in turn evaluates to NULL. Since the WHERE filter evaluates to a non-TRUE value the row is excluded.

The IS NULL operation is specifically designed to test whether a value is NULL and will do what you intend where the equality = operator or IN operator will not.

So:

Boolean Operation Result
----------------- ------
NULL = NULL       NULL
NULL IN ( NULL )  NULL
NULL IS NULL      TRUE

Upvotes: 1

sandman
sandman

Reputation: 2118

  1. DUMMY in DUAL is a varchar2, and you selected all DUMMY from DUAL that are not null (IS NOT NULL). DUAL has one row with DUMMY='X'.
  2. Now you are saying that DUMMY is not IN a set of NULL, which makes no sense really and the select will return no result.

NULL is a special character, and is not zero. It represents the absence of real data (data which includes zeroes and empty spaces).

Upvotes: 0

Related Questions