Use method for is null and is not NULL in Oracle

When the string is empty, it is judged by where null is used.

Example:

CREATE TABLE Test
(
Stuno Number (Ten) primary key,
Stuname VARCHAR2 (50)
)
INSERT into Test (Stuno) VALUES (10)
INSERT into Test (stuno,stuname) VALUES (11, ')

SELECT * FROM test where stuname is NULL to fetch all values.

The SELECT * from Test where stuname= ' is not.

A simple understanding of NULL processing in Oracle

Let's start with a simple description of NULL in Oracle:
1, Oracle null and 0, empty strings, spaces, including null and NULL are unequal.
2. The result of arithmetic operations on NULL in Oracle is still null.
3, the processing method has the NVL function, the comparison method is is null or is not NULL.
4. Null cannot be indexed, such as SELECT COUNT (null) the query result from dual is 0.
5. Null sort is larger than all other types, one can be sorted in empty fields, preceded by null data.

The following examples illustrate:

1,
Sql> Select 1 from dual where null=null; False
No rows selected

Sql> Select 1 from dual where null= '; False
No rows selected

Sql> Select 1 from dual where null=0; False
No rows selected

Sql> Select 1 from dual where ' = '; False
No rows selected

of the above 4 queries, you can see that null is not meant to be understood in Oracle, and it is not equal to any object.

2,
Sql> select 1+null from dual;
1+null
----------

Sql> select 1-null from dual;
1-null
----------

Sql> select 1*null from dual;
1*null
----------

Sql> select 1/null from dual;
1/null
---------
-
Sql> Select 1 from dual where 1+null is null;
1
----------
1
Sql> Select 1 from dual where 1-null is null;
1
----------
1
Sql> Select 1 from dual where 1*null is null;

1
----------
1

Sql> Select 1 from dual where 1/null is null;

1
----------
1

The above is a null subtraction operation, and the subtraction of the visible null is NULL.

3,
Sql> Select 1 from dual where null is null;

1
----------
1

Sql> Select 1 from dual where "is null;

1
----------
1

Sql> Select 1 from dual where NVL (null,0) was not null;

1
----------
1

The above two queries can see that NULL is judged by the NULL,NVL is the function that handles null, and the empty string is considered null.

Tip: null cannot be matched to a% such as Select 1 from dual where null like '% ' is not found.

4,
Sql> select COUNT (null) from dual;

COUNT (NULL)
-----------
0

Sql> Select COUNT (NVL (NULL, 0)) from dual;

COUNT (NVL (null,0))
------------------
1

The above two queries remind you to pay attention to the problem of dealing with null fields according to your own needs at the time of statistics.

Other Notes:

Depending on the definition of NULL in Oracle, NULL is unknown and not equal to NULL, so having more than one null in a unique field in a table does not violate a unique row constraint.

Finally to the various build justice

Is null and is not NULL in the WHERE clause will not use the index but a full table search. Therefore, it is necessary to remove the is null and is NULL in the WHERE clause by changing the query method, discussing the situation, and so on.