Parsing Null values in Oracle

Some people have summarized the concept of Null value in the database, which is more refined. The excerpt is as follows:

Null is a unique data type in the database. If a column in a record is Null, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, Null is not a definite value.

This is the origin and basis of Null. The results of all Null-related operations can be derived from the concept of Null.

To determine whether a field IS Null, use IS Null or is not Null instead of '= '. Null can only be qualitative. It is not Null (IS Null/is not Null) and cannot be set. Simply put, since Null has countless possibilities, the two Null values are not equal, nor can they be said that the two Null values are not equal, or the two Null values are compared, these operations are meaningless and there is no definite answer. Therefore, for Null = ,! The results of operations such as =,>, <, >=, and <= are unknown. Even if the results of these operations are Null.

Similarly, the results of the +,-, *,/, and other operations on Null are unknown, so they are also Null.

Therefore, we often sum up Null in this way. Except for IS Null and is not Null, the result of any operation on Null IS Null.

The Null Value in Oracle is similar to that in SQL Server, but there is a significant difference. In Oracle, the Null String ('') is considered as a Null value. ORACLE allows Null for any data type field, except for the following two cases:

1. primary key field (primary key );

2. fields with the NOT Null restriction added during definition

Let's look at some examples.

1. Null comparison

SQL> select 1 from dual where Null = Null;

-------------------------------------------------------------------------------

No records found

 

 

SQL> select 1 from dual where Null = '';

-------------------------------------------------------------------------------

No records found

 

 

SQL> select 1 from dual where ''= '';

-------------------------------------------------------------------------------

No records found

 

 

SQL> select 1 from dual where Null is Null;

-------------------------------------------------------------------------------

1

 

 

SQL> select 1 from dual where ''is Null;

-------------------------------------------------------------------------------

1

 

 

SQL> select 1 from dual where nvl (Null, 0) = nvl (Null, 0 );

-------------------------------------------------------------------------------

1

2. Null value calculation

Add, subtract, multiply, and divide Null values. The result is still Null. Example

SQL> select 1 + Null from dual;

SQL> select 1-Null from dual;

SQL> select 1 * Null from dual;

SQL> select 1/Null from dual;

 

A record is queried. This record is the Null value in the SQL statement.

3. Trim trap

People who are used to writing T-SQL code can easily write the following error code:

IF v_month IS Null or trim (v_month) =''

THEN

;

End if;

 

If v_month is a space string, a Null value is obtained after trim.

SQL> select 1 from dual where trim ('') = '';

-------------------------------------------------------------------------------

No records found

 

 

SQL> select 1 from dual where trim ('') is Null;

-------------------------------------------------------------------------------

1

4. Null value traps in queries

Create the following test tables and data:

Create table cassb_null

(

Column1 varchar2 (300) not Null,

Column2 varchar2 (300) Null

);

 

Insert into cassb_null values ('1', '1 ');

Insert into cassb_null values ('2 ','');

Commit;

 

Two records are entered. Note that the column2 value of the 2nd records is Null.

 

Query:

SQL> select * from cassb_null where column2 like '% ';

1 record found.

Result description:

The query results indicate that this SQL statement does not display a field with a column value of Null. In this case, the field must be Null.

 

SQL> select * from cassb_null where column2 like '%' or column2 is Null;

SQL> select * from cassb_null where nvl (column2, '0') like '% ';

Two records are queried.

5. Functions for processing Null values in Oracle: NVL ()

Syntax

NVL (eExpression1, eExpression2)

Parameters

EExpression1, eExpression2

If the calculation result of eExpression1 is Null, NVL () returns eExpression2. If the calculation result of eExpression1 is not Null, eExpression1 is returned. EExpression1 and eExpression2 can be any data type. If both eExpression1 and eExpression2 are Null values, NVL () returns Null.

Return Value Type

Numeric, currency, logical, or Null

Description

If Null or Null values are not supported, you can use NVL () to remove Null values in the calculation or operation.

6. Summary and other

1) Null is equivalent to no value and is unknown;

2) add, subtract, multiply, and divide Null. The result is still Null;

3) The NVL function is used for Null processing;

4) use the keyword "is Null" and "is not Null" for comparison;

5) Null cannot be indexed, so some data that meets the query conditions may not be found, count (?) , Use nvl (column name, 0) for processing and then query;

6) sorting is larger than other data (the index is sorted in ascending order by default (ASC), small → large), so the Null value is always at the end.

7) Oracle empty strings are equivalent to Null values. Pay attention to the Null value caused by the Trim space string.