Null in Oracle

In the database, a null value is used to indicate that the actual value is unknown or meaningless. In a table, if a column in a row has no value, it is called NULL ). A null value can be returned for any data type column as long as the non-NULL or primary key integrity restriction is NOT used. In practical applications, ignoring the existence of null values may cause unnecessary troubles.

For example, in the following employee table (EMP), the employee name (ENAME) is KING's Row, because KING is the highest official (PRESIDENT), he has no supervisor (MGR ), therefore, MGR is null. Because not all employees have a commission, column COMM allows null values, and other lines except 300, 500, 1400, and 0 are null values.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7788 scott analyst 7566 09-DEC-82 3000 20
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7876 adams clerk 7788 12-JAN-83 1100 20
7900 james clerk 7698 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10

This article takes the preceding EMP table as an example to discuss the features of null values in daily applications.

I. Generation and features of null values

1. null value generation

If a column has no non-NULL integrity restrictions, the default value is NULL. That is, if the value of this column is NOT specified when a row is inserted, the value is NULL.

INSERT a row using an SQL statement. The values of columns not involved are null. If the values of columns involved are null, during insertion, NULL can be used (for columns of the dense type, ''can also be used ).

For example, insert a row. The values of EMPNO are 1, ENAME is 'jia ', SAL is 10000, and job and comm are null.
SQL> insert into emp (empno, ename, job, sal, comm) values (1, 'jia ', NULL, 1000, NULL );
SQL> select * from emp where empno = 1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------
1, JIA 1000

We can see that the new row is inserted. Except for the null values of job and comm, The mgr, hiredate, and deptno columns are also null because they are not involved during insertion.

Use SQL statement UPDATE to modify data. The NULL value can be expressed by NULL (for columns of the dense type, you can also use ). Example:
SQL> update emp set ename = NULL, sal = NULL where empno = 1;

2. Features of null values

NULL values have the following features:

* It is equivalent to no value.
* It is different from 0, an empty string, or a space.
* In the where condition, if Oracle considers the condition that the result is NULL to be FALSE, select statements with such conditions do not return rows and do not return error messages. But NULL and FALSE are different.
* Sorting is larger than other data.
* Null values cannot be indexed.

Ii. null value test

Because the null value indicates that data is missing, the null value is not comparable with other values, that is, it cannot be compared with other values if it is equal to, not equal to, greater than or less, of course, it also includes the null value itself (except in decode, two null values are considered to be equivalent ). Only the comparison operators is null and is not null can be used to test NULL values. If a conditional expression with other comparison operators is used and the result depends on a NULL value, the result must be NULL. In the where condition, if Oracle considers the condition that the result is NULL to be FALSE, select statements with such conditions do not return rows or error messages.

For example, to query rows whose MGR is NULL in an EMP table:
SQL> select * from emp where mgr = '';
No rows selected
SQL> select * from emp where mgr = null;
No rows selected
SQL> select * from emp where mgr is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------
7839 king president 17-NOV-81 5000 10
1st and 2 sentences are incorrectly written. If the WHERE condition returns NULL, no rows are returned. The third sentence is correct. The row with MGR null is returned.
Iii. null values and operators

1. null values and logical operators

Logical operators
Expression
Result
AND
NULL AND TRUE
NULL

NULL AND FALSE
FALSE

NULL AND NULL
NULL
OR
NULL OR TRUE
TRUE

NULL OR FALSE
NULL

NULL OR NULL
NULL
NOT
NOT NULL
NULL

In the truth table, except null and false, the result is FALSE, NULL, or true, AND all other results are NULL.

Although in the where condition, Oracle considers the WHERE condition with NULL results to be FALSE, NULL is different from FALSE in the condition expression. For example, in NOT (null and false) and not (null and null), there is only one difference between false and true, but NOT (null and false) returns TRUE, the NOT (null and null) result is NULL.

The following example illustrates the usage of null values and logical operators:

SQL> select * from emp where not comm = null and comm! = 0;
No rows selected
SQL> select * from emp where not (not comm = null and comm! = 0 );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------
7844 turner salesman 7698 08-SEP-81 1500 0 30

The first Select statement, condition "not comm = null and comm! = 0 "is equivalent to null and comm! = 0. For any row, if COMM is a value not equal to 0, the condition is equivalent to null and true, AND the result is NULL. If COMM is equal to 0, the condition is equivalent to null and false, AND the result is FALSE. Therefore, the final result does not return rows.

The condition of the second Select statement is the "NOT" (NOT) condition of the first Select statement. For any row, if COMM is a value NOT equal to 0, the condition is equivalent to not null, the result is NULL. If COMM is 0, the condition is equivalent to not false, and the result is TRUE. Therefore, the final result returns a row whose COMM is equal to 0.

2. null values and comparison Operators

(1) IS [NOT] NULL: it IS the unique operator used to test NULL values (see "NULL test ").
(2) = ,! =, >=, <=,>, <
SQL> select ename, sal, comm from emp where sal> comm;
ENAME SAL COMM
----------------------------
ALLEN 1600 300
WARD 1250 500
TURNER 1500 0
If sal or comm is a NULL row and sal> comm is NULL, no rows whose sal or comm is NULL are returned.
(3) IN and not in Operators
SQL> select ename, mgr from emp where mgr in (7902, NULL );
ENAME MGR
-------------------
The SMITH 7902

In the preceding statement, the condition "mgr in (7902, NULL)" is equivalent to mgr = 7902 or mgr = NULL. For any row in the table EMP, if mgr is NULL, the above condition is equivalent to null or null, that is, NULL. If mgr is a value not equal to 7902, the preceding conditions are equivalent to false or null, that is, NULL. If mgr is equal to 7902, the preceding conditions are equivalent to true or null, that is, TRUE. Therefore, the final result returns rows with mgr equal to 7902.

SQL> select deptno from emp where deptno not in ('10', NULL );
No rows selected
In the preceding statement, the condition "deptno not in ('10', NULL)" is equivalent to deptno! = '10' and deptno! = NULL. For any row in the EMP table, the condition result can only be NULL or FALSE, so no row is returned.

(4) any, some
SQL> select ename, sal from emp where sal> any (3000, null );
ENAME SAL
-------------------
KING 5000
The condition "sal> any (3000, null)" is equivalent to sal> 3000 or sal> null. Similar to the first sentence (3) above, the final result returns all rows with sal> 3000.
(5) All
SQL> select ename, sal from E where sal> all (3000, null );
No rows selected
The condition "sal> all (3000, null)" is equivalent to sal> 3000 and sal> null. The result can only be NULL or FALSE, so no row is returned.

(6) (not)
SQL> select ename, sal from emp where sal between null and 3000;
No rows selected
The condition "sal between null and 3000" is equivalent to sal> = null and sal <= 3000. The result can only be NULL or FALSE, so no rows are returned.
SQL> select ename, sal from emp where sal not between null and 3000;
ENAME SAL
-------------------
KING 5000
The condition "sal not between null and 3000" is equivalent to sal <null or sal> 3000. Like the first sentence of (3) above, the row sal> 3000 is returned.
The following table summarizes the comparison operators and null values:
Comparison operator
Expression (for example, expression A and expression B are NULL and expression C = 10)
Result
Is null, IS NOT NULL
A IS NULL
TRUE

A IS NOT NULL
FALSE

C IS NULL
FALSE

C IS NOT NULL
TRUE
= ,! =, >=, <=,>, <
A = NULL
NULL

A> NULL
NULL

C = NULL
NULL

C> NULL
NULL
IN (= ANY)
A in (10, NULL)
NULL

C in (10, NULL)
TRUE

C in (20, NULL)
NULL
NOT IN
(Equivalent! = ALL)
A not in (20, NULL)
NULL

C not in (20, NULL)
FALSE

C not in (10, NULL)
NULL
ANY, SOME
A> ANY (5, NULL)
NULL

C> ANY (5, NULL)
TRUE

C> ANY (15, NULL)
NULL
ALL
A> ALL (5, NULL)
NULL

C> ALL (5, NULL)
NULL

C> ALL (15, NULL)
FALSE
(NOT)
A between 5 AND NULL
NULL

C between 5 AND NULL
NULL

C between 15 AND NULL
FALSE

A not between 5 AND NULL
NULL

C not between 5 AND NULL
NULL

C not between 15 AND NULL
TRUE

3. null, arithmetic, and character Operators

(1) Arithmetic Operator: a null value is not equivalent to 0. Any arithmetic expression containing null values will return NULL values. For example, if a null value is added to 10, it is null.

(2) character operator |: Because ORACLE currently processes zero-character values in the same way as it processes null values (not necessarily in future versions), |, the null value is equivalent to the zero character value. Example:
SQL> select ename, mgr, ename | mgr, sal, comm, sal + comm from emp;
Ename mgr ename | mgr sal comm sal + COMM
-----------------------------------------------------------
SMITH 7902 SMITH7902 800
ALLEN 7698 ALLEN7698 1600 300 1900
WARD 7698 WARD7698 1250 500 1750
JONES 7839 JONES7839 2975
MARTIN 7698 MARTIN7698 1250 1400 2650
Blakke 7839 blak7839 2850
CLARK 7839 CLARK7839 2450
SCOTT 7566 SCOTT7566 3000
KING 5000
TURNER 7698 TURNER7698 1500 0 1500
ADAMS 7788 ADAMS7788 1100
JAMES 7698 JAMES7698 950
FORD 7566 FORD7566 3000
MILLER 7782 MILLER7782 1300
We can see that if mgr is null, ename | mgr results are equal to ename; If comm is null, sal + comm is null.

Iv. null values and functions

1. null values and measurement functions

For a measurement function, if the given parameter is null, its (except NVL and TRANSLATE) return value is null. ABS (COMM) in the following example. If COMM is null, ABS (COMM) is null.
SQL> select ename, sal, comm, abs (comm) from emp where sal <1500;
Ename sal comm abs (COMM)
-------------------------------------
The SMITH 800
WARD 1250 500 500
MARTIN 1250 1400 1400
ADAMS 1100
JAMES 950
MILLER 1300

2. null values and group functions

Group functions ignore null values. In practical applications, nvl functions can be used to replace null values with zero values as needed. Example:
SQL> select count (comm), sum (comm), avg (comm) from emp;
COUNT (COMM) SUM (COMM) AVG (COMM)
-----------------------------
4 2200 550
SQL> select count (nvl (comm, 0), sum (nvl (comm, 0), avg (nvl (comm, 0 ))
From emp;
COUNT (NVL (COMM, 0) SUM (NVL (COMM, 0) AVG (NVL (COMM, 0 ))
--------------------------------------------------
14 2200 157.14286

The first SELECT statement ignores the rows whose COMM is null. The second SELECT statement uses the NVL function to calculate all the COMM values. Therefore, the number and average values of the data are different.

In addition, when using group functions for data processing, different writing methods have different meanings and should be mastered flexibly in practical applications. For example:
SQL> select deptno, sum (sal), sum (comm), sum (sal + comm), sum (sal) + sum (comm), sum (nvl (sal, 0) + nvl (comm, 0 ))
From emp
Group by deptno;
Deptno sum (SAL) SUM (COMM) SUM (SAL + COMM) SUM (SAL) + SUM (COMM) SUM (NVL (SAL, 0) + NVL (COMM, 0 ))
-------------------------------------------------------------------------------------
10 8750 8750
20 10875 10875
30 9400 2200 7800 11600 11600

We can see the differences between SUM (SAL + COMM), SUM (SAL) + SUM (COMM), SUM (NVL (SAL, 0) + NVL (COMM, 0: SUM (SAL + COMM) first adds and then calculates the SUM of all rows. If one of SAL and COMM is NULL, this row is ignored; SUM (SAL) + SUM (COMM) calculate the total number of rows first and then add them. NULL values in SAL and COMM are ignored. However, if SUM (SAL) and SUM (COMM) are NULL, the SUM of the two is NULL. In SUM (NVL (SAL, 0) + NVL (COMM, 0), NULL in SAL and COMM is processed by 0.

5. Other features of null values

1. the null value is greater than any value in sorting. For example:
SQL> select ename, comm from emp where deptno = '30' order by comm;
ENAME COMM
-------------------
TURNER 0
ALLEN 300
WARD 500
MARTIN 1400
BLAKE
JAMES

2. null values cannot be indexed. Although an index is created on a column, the query efficiency of the Column cannot be improved because the null value is not indexed. For example, the following query cannot use the index created on the MGR column.
SQL> select ename from emp where mgr is null;
ENAME
----------
KING

In addition, because null values are not indexed, you can create a unique index (unique index) on a column containing null values ). For example, you can create a unique index on the COMM column of the EMP table:
SQL> create unique index emp_comm on emp (comm );
Index created.