CODEWITHSUNDEEP
sqloracle-database
sunleo
sunleo

Reputation: 10947

Oracle sql null value is not selected

In NAME table FIRST column is having null but no rows are selected. Please help me to understand.

SELECT * FROM NAME WHERE FIRST != '1'

Upvotes: 9

Views: 39154

Answers (5)

AS400Jockey
AS400Jockey

Reputation: 21

NULL is dumb. Period.

NULL is evil.

If X is NULL and Y is NULL, then X does in fact equal Y because they are both NULL.

It's also a PITA that I can't say

IF X IN ('a','B','C', null)

Because this condition happens. But now I have to say 

IF ( X IN ('a','B','C') or X is NULL )

which is a waste of time and a risk of error if I forget the parentheses.

What irks me further is that NULL shouldn't happen in the first place. Fields (er... ok kids, I'll call them Columns) should always be initialized. Period. Stop the nulls. Don't allow them. Default values should always be zeroes or blanks so that those folks that are too lazy to initialize columns in their software will have them initialized for them automatically.

There are many instances where a failure to define default values of zeroes and blanks makes life more difficult than it has to be.

Upvotes: 1

NirmalGeo
NirmalGeo

Reputation: 771

That is correct because NULL can never be compared with anything else....

The only option that you have is to include a NULL check as an or in the command

SELECT * FROM NAME WHERE FIRST!=1 OR FIRST IS NULL

According to Oracle Documentation NULL is defined as a value not knownm or when the value is not meaningful. That is solely the reason why Oracle mentions not consider a value of ZERO as NULL. This is just an FYI, an addon. Thanks!

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Any comparison with null is false - = and <>, >, <, and so on. You cannot use null in an IN list as well - it would be ignored. Moreover, two nulls are not even equal to each other.

To get the nulls, you need to ask for them explicitly, like this:

SELECT * FROM NAME WHERE FIRST IS NULL OR FIRST != '1'

Upvotes: 16

Joe
Joe

Reputation: 63424

In Oracle, null is not considered a legal value to select unless you explicitly ask for it:

select * from name where (first != '1') or first is null

You could also use NVL (similar to coalesce):

select * from name where nvl(first,'0') != '1'

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Any comparison to NULL returns NULL, which is equivalent to FALSE. This is true eve of not-equals.

If you want to include NULL values, do one of the following:

where first <> '1' or first is null

or

where coalesce(first, '<null>') <> '1'

Upvotes: 10

Related Questions