Query to get null values problem

Toad Data Point & Toad Intelligence Central
1

Greetings,

In running a query, a field has several values:
A
B
C
blank or null

With no critera, the blank returns {null}.

My problem, I want to exclude records where this field = C, but I can not get the {null} value records to return.

What I’ve tried:
field = C
field != C
field <> C
field NOT LIKE C (start with, contain, etc)

Any help would be appreciated.

Thank you

2

What is your RDBMS?
This condition should work:
" field is null or field != ‘C’ "

3

The RDBMS is Oracle 11g.

The condition you suggested works well.

Thank you,
Kim.

4

In Oracle you can also write the condition this way, assuming that you know of a value that will NEVER be present in column “field”.

For example, if the column “field” will NEVER contain the value X, you could write the condition as

nvl (field, ‘X’) != ‘C’

which means “if field is null, change it to X, otherwise use the value in field” and “compare the result of the previous expression to the constant C”.

I think the Oracle optimizer will generally find better execution plans with (field is null OR field != ‘C’) rather than (nvl (field, ‘X’) != ‘C’) - UNLESS your DBA has created a function-based index on that table with the expression “nvl (field, ‘X’)” in the function-based index.

It is important to remember that null cannot be compared to a non-null value

The comparison (null = ‘A’) does not return either true or false.
The comparison (null <> ‘A’) does not return either true or false.
The comparison (null = null) does not return either true or false.