A random talk on null values in Oracle

Oracle in the database, null values are used to indicate a situation where the actual value is unknown or meaningless. In a table, if a column in a row has no value, it is called a null value (NULL). Columns of any data type can have null values if they do not use non-null (not NULL) or primary key (PRIMARY key) integrity restrictions. In practical application, if the existence of NULL value is ignored, it will cause unnecessary trouble.
----For example, in the following employee table (EMP), the employee name (ENAME) is the line of King, because King is the top official (PRESIDENT), he has no supervisor (MGR), so its MGR is null. Because not all employees have a handling fee (COMM), the column COMM allows null values, except 300, 500, 1400, 0, all rows COMM are null.
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------------7369 SMITH Clerk 790 2 17-dec-80 207499 ALLEN salesman 7698 20-feb-81 1600 307521 WARD salesman 7698 22-feb-81 1250 307566 JONES MA Nager 7839 02-apr-81 2975 207654 MARTIN salesman 7698 28-sep-81 1250 1400 307698 BLAKE MANAGER 7839 01-may-81 2850 307782 CLARK MANAGER 7839 09-jun-81 2450 107788 SCOTT ANALYST 7566 09-dec-82 3000 207839 KING PRESIDENT 17-nov-81 5000 107844 TUR NER salesman 7698 08-sep-81 1500 0 307876 ADAMS Clerk 7788 12-jan-83 1100 207900 JAMES clerk 7698 03-dec-81 950 307902 for D ANALYST 7566 03-dec-81 3000 207934 MILLER clerk 7782 23-jan-82 1300 10

----This paper takes the EMP table as an example to discuss the characteristics of the null value in the daily application.
Generation and characteristics of----NULL value
----1. Generation of NULL values
----If a column does not have non-null (not null) integrity restrictions, its default value is NULL, that is, if the value of the column is not specified when a row is inserted, the value is a null value.
----Insert rows Using SQL statements, where the value of the column that is not involved is NULL, and the column involved, if its value is indeed null, can be represented by NULL when inserted (for character-type columns, You can also use ' ' to express it.
----Example: Insert a row with Empno 1, ename as ' JIA ', SAL 10000, job, and comm as null values.
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

----You can see the newly inserted row, except for the job and comm, the MGR, HireDate, and DEPTNO columns are also null values because they were not involved in the insert.
----Use SQL statement Update to modify the data, the null value can be represented by null (for character-type columns, or ""). Cases:
SQL >update EMP Set ename=null,sal=null where empno=1;

----2. Characteristics of NULL values
----NULL values have the following characteristics:
----* is equivalent to not having any value.
----* is different from 0, an empty string, or a space.
----* In the Where condition, Oracle considers the result null to be false, and a SELECT statement with such a condition does not return a row and does not return an error message. But null and false are different.
----* is larger than other data when sorting.
----* Null value cannot be indexed.
----two, null value test
----because null values represent missing data, null values and other values are not comparable, i.e. they cannot be compared with equals, not equal, greater than, or less than and other numeric values, including, of course, the null value itself (but in Exception in Decode, two null values are considered equivalent). The test null value can only be used with comparison operator is null and is not NULL. If you use a conditional expression with another comparison operator, and the result depends on a null value, the result must be null. In the Where condition, Oracle considers the condition of the result null to be false, and the SELECT statement with such a condition does not return rows or error messages.
----such as querying MGR Null rows in the EMP table:
SQL >select * from emp where mgr= '; No rows selectedsql >select * from EMP where mgr=null; No rows selectedsql >select * from EMP where Mgr is null; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------7839 KING PRESIDENT 17-nov-8 1 5000 10

----the 1th and 2 sentences are improperly written, where the result is null and no rows are returned. The third sentence is correct and returns the line MGR Null.
----three, null values and operator
----1. Null values and logical operators
----logical operators
----An expression
----results
Andnull and Truenullnull and Falsefalsenull and Nullnullornull or Truetruenull or Falsenullnull or Nullnullnotnot

----can see that in the truth table, except for null and false, the result is false, null or TRUE, and the other result is null.
----Although in the Where condition Oracle thinks the result is null where condition is false, NULL differs from false in the conditional expression. For example, there is only one FALSE and true difference between not (null and false) and not (null and NULL), but the result of not (null and false) is true, and The result of not (null and NULL) is NULL.
----The following examples illustrate the use of NULL values and logical operators:
SQL > select * from emp where not comm=null and comm!=0;no rows selectedsql > select * to EMP where not comm =null and Comm!=0); EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----------------------------------------------7844 TURNER salesman 7698 0 8-SEP-81 1500 0 30

----The first SELECT statement, the condition "not comm=null and comm!=0" is equivalent to null and comm!=0. For any row, if Comm is a numeric value that is not equal to 0, the condition is equivalent to null and TRUE, the result is null, and if Comm equals 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 "not" for the first SELECT statement condition, and for any row, if Comm is a numeric value that is not equal to 0, the condition is equivalent to not NULL, and the result is NULL; if comm equals 0, the condition is equivalent to not FALSE, and the result is true. So, the final result returns the row comm equals 0.
----2. Null values and comparison operators
----(1) is [not] NULL: The only operator used to test null values (see "Test for null Values").
(2) =,!=, >=, <=, >, <sql >select ename,sal,comm from emp where Sal >comm; ename SAL COMM----------------------------ALLEN 1600 300WARD 1250 500TURNER 1500 0

----SAL or COMM is a null-valued row, the Sal>comm comparison is null, so the rows that are either SAL or COMM NULL are not returned.
----(3) in and not in operator
SQL >select ename,mgr from EMP where Mgr in (7902,null); ename MGR-------------------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, or NULL, and if MGR is a numeric value that is not equal to 7902, the above condition is equivalent to false O R null, NULL, or True if Mgr equals 7902. The above condition is equivalent to True OR NULL. So, the end result can return rows that are equal to 7902 Mgr.
SQL >select deptno from EMP where Deptno is not in (' a ', NULL); no rows selected

----in the preceding statement, the condition "Deptno not In" (', NULL ') is equivalent to deptno!= ' and deptno!=null, and for any row in the EMP table, the result of the condition can only be NULL or FALSE, so no rows are returned.
----(4) Any,some
SQL >select ename,sal from emp where Sal > any (3000,null); ename SAL-------------------KING 5000

----condition "Sal > any (3000,null)" is equivalent to Sal >3000 or Sal >null. Similar to the first sentence of the preceding paragraph (3), the final result returns all Sal >3000 rows.
----(5) All
SQL >select ename,sal from emp where Sal > All (3000,null); no rows selected

The----condition "sal> all" (3000,null) is equivalent to Sal >3000 and Sal >null, and the result can only be null or FALSE, so no rows are returned.
----(6) (not) between
SQL >select ename,sal from emp where sal between null and 3000;no rows selected

----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

----condition "Sal not between null and 3000" is equivalent to sal3000, similar to the first sentence of the preceding (3), and the result returns the sal>3000 row.
----The following table is a summary of comparison operators and Null values:
----comparison operator
----expression (example: A, B is null, c=10)
----results
Is NULL, is not NULLA are Nulltruea is isn't nullfalsec is nullfalsec are not nulltrue=,!=, >=, < =, >, < A = Nullnulla > NULLNULLC = NULLNULLC > Nullnullin (=any) A in (10,null) NullC in (10,null) Truec in (20,null) Nullnot in (equivalent to!) =all) A not in (20,null) NULLC not in (20,null) Falsec No in (10,null) Nullany,somea > any (5,null) NULLC > any (5,null) TR UEC > Any (15,null) Nullalla > All (5,null) NULLC > All (5,null) NULLC > All (15,null) FALSE (not) Betweena BETWEEN 5 A ND NULLNULLC BETWEEN 5 and NULLNULLC BETWEEN and Nullfalsea not BETWEEN 5 and NULLNULLC no BETWEEN 5 and NULLNULLC not BETWEEN and Nulltrue

----3, null value and arithmetic, character operators
----(1) Arithmetic operators: null values are not equivalent to 0, and any arithmetic expression with a null value evaluates to a null value, such as a null value plus 10 null.
----(2) character operator | | : Because Oracle currently handles 0 character values in the same way as a null value (not necessarily in future versions), null values are equivalent to 0 character values for | |. Cases:
SQL >select ename,mgr,ename| | Mgr,sal,comm,sal+comm from EMP; ename MGR ename| | MGR SAL COMM sal+comm-----------------------------------------------------------SMITH 7902 SMITH7902 ALLEN 7698 A LLEN7698 1600 1900WARD 7698 WARD7698 1250 1750JONES 7839 JONES7839 2975 MARTIN 7698 MARTIN7698 1250 1400 2650BLAKE 7839 BLAKE7839 2850 CLARK 7839 CLARK7839 2450 SCOTT 7566 SCOTT7566 3000 King 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, where Mgr is null, ename| | The MGR result is equal to ename, and the sal+comm is null for all rows comm to null values.
----four, null values and functions
----1. Null values and Measurement functions
----for Measure functions, if the given argument is a null value, its return value is null (except NVL, TRANSLATE). ABS (COMM) in the following example, if COMM is a null value, ABS (COMM) is null.
SQL > select Ename,sal,comm,abs (Comm) from EMP where sal< 1500; ename SAL COMM ABS (COMM)-------------------------------------SMITH 800WARD 1250 500MARTIN 1250 1400 1400ADAMS 1100J AMES 950MILLER 1300

----2. Null values and group functions
----Group function ignores null values. In practical application, the NVL function can be used to replace null value with 0. Cases:
SQL >select count (comm), SUM (comm), AVG (comm) from EMP; Count (COMM) sum (COMM) AVG (COMM)-----------------------------4 2200 550SQL >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 rows with Comm as null values, the second SELECT statement uses the NVL function to count all comm, so they are statistically different in number and average. In addition, in the use of group functions to data processing, different writing has different meanings, in the practical application should be flexible master. e.g.
SQL >select Deptno,sum (SAL), SUM (comm), SUM (Sal+comm), sum (SAL) +sum (comm), SUM (NVL (sal,0) +NVL (comm,0)) from Empgroup 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

----can see the difference between sum (SAL+COMM), sum (SAL) +sum (COMM), SUM (NVL (sal,0) +NVL (comm,0)): Sum (SAL+COMM) is first added and then computes the sum of each row, if the SA If one of the L, COMM is null, the row is ignored; sum (SAL) +sum (COMM) calculates the totals for each row and then adds the null in Sal, COMM, but if sum (sal ), sum (COMM) is null in the result of both, and Null in sum (NVL (sal,0) +NVL (comm,0)), and Null in SAL, COMM is treated as 0.
----Other characteristics of NULL value
----1. A null value is greater than any value when sorting. e.g.
SQL > select Ename,comm from emp where deptno= ' ORDER by Comm;ename Comm-------------------TURNER 0ALLEN 300WARD 50 0MARTIN 1400BLAKE JAMES

----2. Null values cannot be indexed. Although an index is established on a column, it is not possible to improve the efficiency of the query because null values are not indexed for the null value query for that column. For example, the following query cannot take advantage of 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, a unique index (unique index) can be established on a column that contains null values. For example, you can establish a unique index on the Comm column of the EMP table:
SQL > Create unique index emp_comm on EMP (comm), Index created.