Check for != values when column contains records with NULLs

Question

I have a number of tables that mix 'real' values with nulls in columns. From exerience, issuing a SELECT against these that looks like:

SELECT column1, column2, column3 FROM mytable WHERE column1 != 'a value';

...doesn't return the records I expect. In the current table I am working on, this returns an empty recordset, even though I know I have records in the table with NULLs in column1, and other records in the table that have the value I am "!="ing in column1. I am expecting, in this case, to see the records with NULLs in column1 (and, of course, anything else if there were other not 'a value' values in column1.

Experimenting with NVL in the WHERE clause doesn't seem to give me anything different:

SELECT column1, column2, column3 FROM mytable WHERE NVL(column1, '') != 'a value';

...is also returning an empty recordset.

Using 'IS NULL' will technically give me the correct recordset in my current example, but of course if any records change to something like 'another value' in column1, then IS NULL will exclude those.

The reason why your experiment didn't work is that in Oracle, `''` is itself `null`. — Jeffrey Kemp, Jul 26 '17 at 05:48

score 3 · Answer 1 · answered Jul 26 '17 at 03:41

3

NULL can't be compared in the same way that other values can be. You must use IS NULL. If you want to include NULL values, add an OR to the WHERE clause:

SELECT column1, column2, column3 FROM mytable WHERE column1 != 'a value' OR column1 IS NULL

answered Jul 26 '17 at 03:41

kaineub

162
10

score 2 · Answer 2 · answered Jul 26 '17 at 03:40

The query doesn't work because SQL handles equality checks (!=) different from checking if null (IS NULL).

What you could do here is something like:

SELECT column1, column2, column3 
FROM mytable 
WHERE column1 != 'a value' OR column1 is null;

See Not equal <> != operator on NULL.

score 1 · Answer 3 · answered Jul 26 '17 at 06:02

1

What you were trying was correct. You just need change it a little. see below-

SELECT column1, column2, column3 FROM mytable WHERE NVL(column1, '0') != 'a value';

Instead of empty string, pass any character in NVL's second argument.

answered Jul 26 '17 at 06:02

Himanshujaggi

391
1
9

score 0 · Answer 4 · answered Jul 26 '17 at 05:56

"Experimenting with NVL in the WHERE clause doesn't seem to give me anything different"

That's true:

SQL> select * from mytable;

COLUMN1                 COLUMN2 COLUMN3
-------------------- ---------- ---------
a value                       1 25-JUL-17
not a value                   2 25-JUL-17
whatever                      3 25-JUL-17
                              4 26-JUL-17

SQL> SELECT column1, column2, column3 FROM mytable WHERE NVL(column1, '') != 'a value';

COLUMN1                 COLUMN2 COLUMN3
-------------------- ---------- ---------
not a value                   2 25-JUL-17
whatever                      3 25-JUL-17

SQL>

This is because your experiment didn't go far enough. For historical reasons Oracle treats an empty string as null so your nvl() statement effectively just subs one null for another. But if you had used a proper value in your call you would have got the result you wanted:

SQL> SELECT column1, column2, column3 FROM mytable WHERE NVL(column1, 'meh') != 'a value';

COLUMN1                 COLUMN2 COLUMN3
-------------------- ---------- ---------
not a value                   2 25-JUL-17
whatever                      3 25-JUL-17
                              4 26-JUL-17

SQL>

The alternative approach is to explicitly test for NULL and test for the excluding value...

SQL>  SELECT column1, column2, column3 FROM mytable
  2  where column1 is null or column1 != 'a value';

COLUMN1                 COLUMN2 COLUMN3
-------------------- ---------- ---------
not a value                   2 25-JUL-17
whatever                      3 25-JUL-17
                              4 26-JUL-17

SQL>

The second approach is probably more orthodox.

score 0 · Answer 5 · answered Jul 26 '17 at 07:14

1) Undocumented Oracle function SYS_OP_MAP_NONNULL. (It exists from oracle10)

with abc as ( select 'a value' as col1 from dual
                union all  
               select '' as col1 from dual)
      select * from abc
       where SYS_OP_MAP_NONNULL(col1) != SYS_OP_MAP_NONNULL('a value')      
      ;

2) LNNVL - Check table in the documentation for clarification.

with abc as ( select 'a value' as col1 from dual
                union all  
               select '' as col1 from dual)
      select * from abc
       where lnnvl( col1 = 'a value');    
      ;

Check for != values when column contains records with NULLs

5 Answers5