I wrote a SQL to query table mat from an oracle db where column A is not null. Column A is varchar and its default value is ' '. I wrote the sql below:

select * from mat where matnr='test' and A <>'' and A <> ' '

But it return an empty data set.

Then I ran:

select * from mat where matnr='test' and A <> ' '

This query worked. So what is the reason? Thx.

CodePudding user response：

In Oracle, '' means NULL. Any direct comparison to NULL returns NULL instead of TRUE or FALSE, so you cannot say A <> '' - you must say A IS NOT NULL.

Another possibility would be to use the NVL function, replacing NULL with ' ', so that you could say

select * from mat where matnr='test' and NVL(A, ' ') <> ' '