Oracle select query does not fetch records where value is null

Question

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.

Marmite Bomber · Accepted Answer · 2020-10-06 15:58:50Z

2

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")

edited Oct 6, 2020 at 15:58

answered Oct 6, 2020 at 15:46

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

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

2 Comments

Ashish Over a year ago

Thanks for the explanation.

Marmite Bomber Over a year ago

@Ashish Unfortunately not as simple. SQL implements a three value logik with TRUE, FALSE and UNKNOWN, so you can't imply not true --> false. See overview in the docs

Matthew McPeak · Accepted Answer · 2020-10-06 17:38:03Z

2

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.

edited Oct 6, 2020 at 17:38

answered Oct 6, 2020 at 15:52

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

1 Comment

Marmite Bomber Over a year ago

This is not correct as documented here. 1 = null is UNKNOWN and not false. If it would be false the negation of it (NOT) would be true, which it is not! Please adjust to not spread confusion in an already confusing logik;)

Collectives™ on Stack Overflow

Oracle select query does not fetch records where value is null

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related