In the data library, null value is used to indicate the actual value unknown or meaningless. In a table, if there is no value in one column in one line, it is called a null value (NULL). Any data type column, only has a null value in a NOT NULL or a Primary Key. In active applications, if you want to ignore the sailing, it will make it unnecessary.
---- Example, in the lower employee table (EMP), the employee name (ENAME) is King, because King is the highest official member (President), he did not have a main tube (MGR), and its MGR is empty. Because of all employees have a fee (COMM), the column Comm is allowed to have a null value, except for the 300, 500, 1400, and each of its lines are empty.
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
.
---- One, nature of the generation and characteristics
---- 1. Empty value
---- If there is no NOT NULL, it is no NULL, which is a null value, that is, if the value is not specified when inserting a line, its value is empty. ---- Use the SQL statement insert to insert the row, where the column not involved, its value is null; the column involved, if its value is indeed, you can use null to reply (for the character type You can also use '' to be represented).
---- Example: Insert a line, its Empno is 1, ename is 'JIA', SAL is 1000, Job and COMM a null value.
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 line, except for Job and COMM, MGR, HiRedate, DEPTNO three columns are not involved in insertion, and is also empty.
---- Use the SQL statement update to modify the data, the null value can be used to indicate (for the characterized column, can also be used with ''). example:
SQL> Update Emp Set Ename = null, SAL = NULL WHERE EMPNO = 1;
---- 2. Features of null value
---- Air value has the following features:
---- * Isometrically not anything.
---- * is different from 0, empty string or empty.
---- * In Where condition, Oracle recognizes the condition of NULL to false, with the SELECT statement with this condition does not return, and does not return error messages. But NULL and FALSE are different.
---- * Data is larger than its data.
---- * Null value cannot be indexed.
---- Two, null test
---- Since null value indicates that the data is missing, null value and other values are not comparable, that is, it cannot be used equal to, not equal, greater than or less than or other numerical comparisons, but also including null values (but in decode, Two null values are considered equivalent). The test null value can only be used than the more operator IS NULL and IS NOT NULL. If you use it with which it is a conditional table of the more manner, and its results depend on null value, the results must be NULL. In the WHERE condition, Oracle recognizes the strip of NULL is false, and the SELECT statement with this condition does not return to the back, nor will the wrong message. ---- Example Queue in the EMP table in the EMP table row of NULL:
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
---- The 1st, 2 sentences are not appropriate, where conditions are NULL, do not return. The third sentence is correct, returning to the MGR of the empty value.
---- Third, null and operator
---- 1. Null value and logic operation
---- Logic Operation
---- Table Delivery
---- Fruits
AND
Null and true
NULL
Null and false
False
Null and Null
NULL
Oral
Null or true
True
Null or false
NULL
Null or Null
NULL
NOT
NOT NULL
NULL
---- It can be seen that in the true value table, in addition to Null and false results, NULL or TRUE results are True, and its results are NULL.
---- Although in WHERE conditions, Oracle recognizes the results of NULL's WHERE condition, but NULL is not fals all in the conditional table. For example, only one of the FALSE and TRUE in NOT (NULL AND FALSE) and NOT (NULL AND NULL), but the result of NULL AND FALSE is TRUE, and the result of Null and Null is NULL. .
---- Under the way, the usage of empty values and logo operations:
SQL> SELECT * from Emp where not comm = null and common! = 0;
NO ROWS SELECTEDSQL> 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, the condition "Not Comm = NULL AND COMM! = 0" is equivalent to NULL AND COMM! = 0. For any one, if the COMM is not equal to 0, the condition is equivalent to NULL AND TRUE, the result is null; if a COMM is equal to 0, the condition is equivalent to NULL AND FALSE, the fruit is false. In this case, the final result is not returned.
---- The condition of the second SELECT statement is "non" (not) of the first SELECT statement condition, for any line, if the COMM is not equal to 0, the condition is equivalent to NULL, the result is null; If the COMM is equal to 0, the condition is equivalent to NOT FALSE, the result is TRUE. In the case, the final result returns to the line of COMM and other rows.
---- 2. Null value and comparison
---- (1) IS [not] null: is the only manager of the test null value (see "Test Test" of "Air Value").
(2) =,! =,> =, <=,>, <
SQL> SELECT ENAME, SAL, COMM from Emp Where Sal> Comm
ENAME SAL COMM
---------- -----------------
Allen 1600 300
Ward 1250 500
Turner 1500 0
---- SAL or COMM is a null, Sal> Comm is NULL than the fruit, and there is no return to the row of Sal or CommM's null.
---- (3) IN and NOT IN Operations
SQL> SELECT ENAME, Mgr from Emp Where Mgr in (7902, NULL);
ENAME MGR
---------- ---------
Smith 7902
---- In the above statement, the condition "Mgr IN (7902, NULL)" is equivalent to Mgr = 7902 or Mgr = NULL. For any line in the table EMP, if the MGR is NULL, the above condition is equivalent to NULL or NULL; if the MGR is not equal to 7902, the above condition is equivalent to false or null, that is, NULL; If the MGR is equal to 7902, the above conditions are equivalent to True or Null, which is TRUE. In the case, the final result can return MGR equal to 7902. SQL> SELECT Deptno from Emp Where Deptno Not in ('10', NULL);
No rows selected
---- In the above statement, the condition "DePTNO Not in ('10', NULL)" is equivalent to deptno! = '10' and deptno! = Null, for any line in the EMP table, the results of the conditions can only For NULL or FALSE, it is not returned.
---- (4) Any, Some
SQL> SELECT ENAME, SAL from Emp Where Sal> Any (3000, NULL);
ENAME SAL
---------- ---------
King 5000
---- Word "Sal> ANY (3000, NULL)" Isometric Sal> 3000 or Sal> Null. The class like the foregoing (3), the final result returns to all the rows of the SAL> 3000.
---- (5) All
SQL> SELECT ENAME, SAL from Emp Where Sal> All (3000, NULL);
No rows selected
---- Conditions "Sal> All (3000, NULL)" Isometric Sal> 3000 and Sal> NULL, the result can only be NULL or FALSE, and do not return.
---- (6) (not) Between
SQL> SELECT ENAME, SAL from Emp Where Sal Between Null and 3000;
No rows selected
---- SALBEEN NULL AND 3000 is equivalent to Sal> = null and SAL <= 3000, the result can only be NULL or FALSE, so that you don't return.
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL NOT BETWEEN NULL AND 3000;
ENAME SAL
---------- ---------
King 5000
---- Condition "Sal Not Between Null and 3000" equivalent to SAL
3000, the first sentence of the aforementioned (3), the result returns to the row of Sal> 3000. ---- The following table is smaller than that of the more manifest and null value:
---- More than active
---- Table Decan (Example: A, B is NULL, C = 10)
---- Fruits
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) Between
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 and calculation, character operation
---- (1) An operation of an algorithmic operation: Anyone is not equivalent to 0. Any irregular value-based algorithography has an empty value, such as a null value plus 10 is null.
---- (2) Character Operation ||: Because Oracle Currently Processes the Method of Treatment of Zero Characters The same as the processing null value (this is not necessarily the same in future versions), so for ||, null value equivalent 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 2975martin 7698 martin7698 1250 1400 2650
Blake 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
---- I can see that when MGR is empty, ename || MGR results are equal to ENAME; where the COMM is null, the Sal COMM is empty.
---- Four, null and functions
---- 1 . Null value and metric function
---- For the metric function, if the given parameter is a null value, the return value is null (NVL, Translate). As the ABS (COMM) in the following example, if the COMM is empty, ABS (COMM) is a null value.
SQL> SELECT ENAME, SAL, COMM, ABS (COMM) from Emp WHERE SAL <1500
ENAME SAL COMM ABS (COMM)
---------- -------------------------
Smith 800
Ward 1250 500 500
Martin 1250 1400 1400
ADAMS 1100
James 950
Miller 1300
---- 2. Null value and group function
---- Group function ignores the slightly null value. In active applications, the NVL function must be used for null values. 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 line of the common value, the second SELECT statement is used to use the NVL function to count all the CommM, and the number, the average value is not as the number. In addition, it is necessary to note that when the use group function is applied, the different write law is different, and it should be in practice in practice. 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 (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
---- You can see SUM (Sal COMM), SUM (SAL), SUM (NVL (SAL, 0) NVL (Comm, 0)): SUM (SAL COMM) is First, then calculate the sum of each line, if there is a NULL in Sal, COMM, the line is ignored; SUM (SAL) SUM (COMM) is first calculating the total of each line and then plus, SAL, NULL in Sal, CommM Ignore, but if there is a null in the results of SUM (SAL), SUM (COMM), then the sum is null; in SUM (NVL (SAL, 0) NVL (COMM, 0)) NULL in SAL, and CommM Press 0.
---- Five, the null value of its characteristics
---- 1. The null value is greater than any value when sequencing. E.g:
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 value cannot be indexed. Although the index is established on a certain column, it is the empty value check of the column. Because the null value is not indeed, the efficiency of the compliance cannot be improved. Examples of the following queries cannot be used in the MGR column.
SQL> SELECT Ename from Emp Where MGR is NULL;
ENAME
------------
King
---- Another additionally because the null value is not indeed, the unique index can be established on the column containing null value. For example, it can be established on the COMM column of the EMP table: SQL> CREATE UNIQUE INDEX EMP_COMM ON EMP (Comm);
Index created.
