Oracle NULL indicates that the meaning is uncertain, so the uncertain things will also be definite.

Oracle NULL represents uncertainty. Will there be a definite data type for uncertain items? Alternatively, let's change the default data type of NULL in Oracle. Here we will discuss this issue. First, I will announce the answer. The default type of NULL is the character type, specifically VARCHAR2 or CHAR. This is not clear, but I personally suspect

Oracle NULL represents uncertainty. Will there be a definite data type for uncertain items? Alternatively, let's change the default data type of NULL in Oracle. Here we will discuss this issue. First, I will announce the answer. The default type of NULL is the character type, specifically VARCHAR2 or CHAR. This is not clear, but I personally suspect

Oracle NULL represents uncertainty. Will there be a definite data type for uncertain items? Alternatively, let's change the default data type of NULL in Oracle. Here we will discuss this issue.

The answer is as follows: the default type of NULL is the character type, specifically VARCHAR2 or CHAR. This is not clear, but I personally suspect that it is more likely to be VARCHAR2.

We know that NULL values can be inserted for a field of any type, that is, NULL can be converted to any type at will.

In addition, the input value of most functions is NULL and the returned result is NULL, which prevents us from trying to judge the NULL type through the return result of the function. We usually use the DUMP function to analyze data, which is also effective:

SQL> SELECT DUMP (NULL) FROM DUAL;

DUMP
----
NULL

It is also impossible to determine the NULL type by using create table:

SQL> CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB;
Create table t as select tname, NULL COL1 FROM TAB
*
ERROR is located in row 1st:
ORA-01723: Columns with 0 length not allowed

Some people may wonder how do you obtain the default NULL type since the methods do not work? Some people may think that, since NULL can be implicitly converted to any type, is it meaningful to discuss the default type of NULL?

The following is an example of the NULL data type I found. It also shows the possible problems if the NULL data type is not noticed.

The original SQL statement is too complex. Here is a simplified example.

SQL> create table t (id number );

The table has been created.

SQL> insert into t values (1 );

One row has been created.

SQL> insert into t values (8 );

One row has been created.

SQL> insert into t values (0 );

One row has been created.

SQL> insert into t values (15 );

One row has been created.

SQL> commit;

Submitted.

The data needs to be displayed in ascending order of the ID in T. The SQL statement is as follows:

SQL> select * from t order by id;

ID
----------
0
1
8
15

There is a small requirement. For a special value of 0, it is displayed after all non-0 values. Of course, there are many implementation methods, such as separating non-0 values from 0 values using union all, or converting 0 values into a large value.

Because the maximum value of the ID is unknown and a simple SQL statement is used to complete the process, I chose to convert the value 0 to NULL during sorting. In this way, the principle of the maximum value of NULL in sorting is used, get the expected result.

The SQL statement is as follows:

SQL> select * from t order by decode (id, 0, null, id );

ID
----------
1
15
8
0

The 0 value is indeed at the end as I wish, but the result is "wrong!

SQL> select decode (id, 0, null, id) from t;

DECODE (ID, 0, NULL, ID)
----------------------------------------
1
8

15

Look at the result of the DECODE function. This time I understand that the result of the original DECODE is changed to the character type. The character type results are left aligned in SQLPLUS, while the value type is right aligned.

In the DECODE function, two IDs and 0 of the four input parameters are of the NUMBER type. The type of the input value is unknown only when NULL is input. Is it because NULL is of the character type?

The guess is just a guess, and the exact evidence is needed to prove this. Let's take a look at the definition of the DECODE function in the standard package.

The following DECODE function definition is a part of the content extracted from STANDARD:

Function DECODE (expr NUMBER, pat NUMBER, res NUMBER) return NUMBER;
Function DECODE (expr NUMBER,
Pat NUMBER,
Res VARCHAR2 character set ANY_CS)
Return VARCHAR2 character set res % CHARSET;
Function DECODE (expr NUMBER, pat NUMBER, res DATE) return DATE;
Function DECODE (expr VARCHAR2 character set ANY_CS,
Pat VARCHAR2 character set expr % CHARSET,
Res NUMBER) return NUMBER;
Function DECODE (expr VARCHAR2 character set ANY_CS,
Pat VARCHAR2 character set expr % CHARSET,
Res VARCHAR2 character set ANY_CS)
Return VARCHAR2 character set res % CHARSET;
Function DECODE (expr VARCHAR2 character set ANY_CS,
Pat VARCHAR2 character set expr % CHARSET,
Res DATE) return DATE;
Function DECODE (expr DATE, pat DATE, res NUMBER) return NUMBER;
Function DECODE (expr DATE,
Pat DATE,
Res VARCHAR2 character set ANY_CS)
Return VARCHAR2 character set res % CHARSET;
Function DECODE (expr DATE, pat DATE, res DATE) return DATE;

By observing the above definition, we can easily find that, although Oracle has carried out a lot of overloading of the DECODE function and the DECODE function supports various data types, the DECODE function has a regular pattern, that is, the type of the return value of the DECODE function is the same as the data type of the first parameter to be returned in the input parameter of the DECODE function. It may not be easy to understand. Here is a simple example:

SQL> select decode (id, 1, '1', 2) from t;

D
-
1
2
2
2

SQL> select decode (id, '1', 1, '2') from t;

DECODE (ID, '1', 1, '2 ')
--------------------
1
2
2
2

From these two simple examples, we can see that the data type returned by DECODE is the same as the data type of the first return parameter in the DECODE function.

From this point, we can see that the default number type of NULL is the character type, which leads to the result of DECODE becoming a string, and the query is sorted and compared according to the string, therefore, '15' is less than '8 '.

After knowing the cause of the problem, there are many solutions, such:

SQL> select * from t order by decode (id, 1, 1, 0, null, id );

ID
----------
1
8
15
0

SQL> select * from t order by to_number (decode (id, 0, null, id ));

ID
----------
1
8
15
0

SQL> select * from t order by decode (id, 0, cast (null as number), id );

ID
----------
1
8
15
0

SQL> select * from t order by decode (id, 0, to_number (null), id );

ID
----------
1
8
15
0