1

I have made a new column in my database table:

alter table TABLE add (COLUMN NUMBER(1));

Now I want to select all records where the value of column is not zero, either it’s some other number of empty (null)

SELECT * FROM TABLE WHERE COLUMN != 0;

And by default all columns have empty/null value as this column is recently added so it should return all records but doesn’t return any.

But if a change the value for this column of a record to 0 and run below query it works fine.

SELECT * FROM TABLE WHERE COLUMN = 0;

Please help I want to select all records where the value is not 0.

Maven
  • 14,587
  • 42
  • 113
  • 174

1 Answers1

6

For NULL values you have to explicitly give IS NULL or IS NOT NULL

SELECT * FROM TABLE WHERE COLUMN <> 0 OR COLUMN IS NULL;

OR 

SELECT * FROM TABLE WHERE NVL(COLUMN,1) <> 0

The Optimizer is going to do like in the first case even if you go with NVL()

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • You state "The Optimizer is going to do like in the first case even if you go with NVL()". Can you give a reference that documents this? You make me doubt, but I *think* this is not true. – Kim Berg Hansen Oct 09 '14 at 13:30