CODEWITHSUNDEEP
oracle-databaseoracle11g
Ashish
Ashish

Reputation: 1917

Oracle select query does not fetch records where value is null

I have an oracle DB query where I want to fetch all such records from a table where the value of DECISION is not equal to DISCARD. I ran the below query:

select * from TABLE where DECISION != 'Discard'

I had two records in my database but for both the DECISION was null ,which is not equal to DISCARD. However, I did not get any record.

So, I had to change the query to below which fetched the records successfully.

select * from TABLE where (DECISION != 'Discard' or DECISION is null)

I am confused about what is the difference between the two.

Upvotes: 0

Views: 1183

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17924

By design / definition, NULL is a special value indicating unknown/inapplicable. Any comparison made to NULL is UNKNOWN, which in Oracle's three-valued logic (https://en.wikipedia.org/wiki/Three-valued_logic) is neither TRUE nor FALSE.

Your 1st predicate:

where DECISION != 'Discard'

evaluates to UNKNOWN for records having a NULL decision. The WHERE clause only includes rows where the predicate is TRUE. Since UNKNOWN is not the same as TRUE, your 1st predicate excludes NULL values.

Your 2nd predicate:

where (DECISION != 'Discard' or DECISION is null)

Evaluates to (UNKNOWN OR TRUE), which in turn evaluates to TRUE. So, your 2nd predicate includes the NULL values.

Upvotes: 2

Marmite Bomber
Marmite Bomber

Reputation: 21073

You may use NVL

select *  from TABLE where NVL(DECISION,'x') != 'Discard'

The "problem" is in the three value SQL logik

if DECISION is NULL than DECISION != 'Discard' returns not TRUE but UNKNOWN (it means it is not known if DECISION is Discard or not).

See details in Oracle Documentation

a != NULL gives UNKNOWN for any value of a

To get all rows testing a nullable column you must use NVL or possible COALESCE (= more "standard")

Upvotes: 2

Related Questions