Geek asks and answers

Invalid Oracle sql value not selected

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

SELECT * FROM NAME WHERE FIRST != '1'
+5
source share
4 answers

Any comparison with the nullfalse - =and <>, >, <etc. You cannot use nullthe list IN, but it will be ignored. Moreover, the two are nullnot equal to each other.

To get zeros, you need to request them explicitly, for example:

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

Any comparison with NULL returns NULL, which is equivalent to FALSE. This is the truth of no equal.

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'
+9
source

Oracle null , :

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

NVL ( ):

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

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

The only option you have is to enable NULL checking as or in a command

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

According to Oracle Documentation, NULL is defined as a value not known, or when the value does not make sense. This is the only reason Oracle does not treat the value of ZERO as NULL. This is just FYI, an addon. Thank you

+2
source

More articles:


All Articles