Empty values in Oracle

In the data warehouse, null values are used to indicate conditions in which the actual values are unknown or ambiguous. In a table, if a column in a row does not have a value, it is called null ). For any data-type column, you only need to use the non-null (not null) or primary key to finish the integer restriction, can be empty. In practice, if a null value is stored, it will become unnecessary.

---- For example, in the following employee table (EMP), the employee name (ename) is King's Row, because King is the highest officer (President ), he does not have the primary Manager (MGR), and its MGR is null. Because not all employees have a manual fee (Comm), the column comm allows null values, except for 300, 500, 1400, and 0, its comm lines are empty values.

EMPNO ENAME    JOB       MGR HIREDATE    SAL   COMM  DEPTNO---- -------- -------- --------- -------- ------ ---------7369 SMITH     CLERK     7902 17-DEC-80  800           207499 ALLEN     SALESMAN  7698 20-FEB-81  1600    300   307521 WARD      SALESMAN  7698 22-FEB-81  1250    500   307566 JONES     MANAGER   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 TURNER    SALESMAN  7698 08-SEP-81  1500      0   307876 ADAMS     CLERK     7788 12-JAN-83  1100          207900 JAMES     CLERK     7698 03-DEC-81   950          307902 FORD      ANALYST   7566 03-DEC-81  3000          207934 MILLER    CLERK     7782 23-JAN-82  1300          10

---- This article will take the above EMP table as an example to discuss some of the characteristics of a null value in daily use.

---- 1. Generate null values and their special points

---- 1. Generate null values

---- If a column does not have an integer limit of non-null (not null), the value of its province is null, that is, if the value of this column is not specified when a row is inserted, the value is null.

---- Insert into the row using SQL statements. The values of the columns not involved in the insert operation are null values, if the value is indeed a null value, it can be expressed as null during insertion (for character-type columns, you can also use ''for table display ).

---- For example, insert a row. The values of empno are 1, ename is 'jia ', Sal is 10000, and job and comm are empty.

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 that the newly inserted rows are not involved in the Mgr, hiredate, and deptno columns except for the null values of job and comm, it is also a null value.

---- Use SQL statement update to modify data. null values can be used to represent data, you can also use ''for table display ). Example:

SQL >update emp set ename=NULL,sal=NULL where empno=1;

---- 2. null value feature

---- Null values have the following special points:

---- * What is the value of the equal price.

---- * It is different from 0, null character string, or null lattice.

---- * In the WHERE clause, if Oracle determines that the result is null and false, the Select clause with this clause will not return to the row, no error message is returned. However, null and false are different.

---- * Sorting is larger than other data.

---- * Null values cannot be cited.

---- 2. Empty Value Test

---- Because the empty value table shows that there are few data missing, the null value and its value are not comparable, that is, it cannot be used for equal to, not equal to, greater than or less than a comparison with its number value, however, it also includes the null value itself (except in decode, the two null values are considered equal price ). The null values of the test can only use the comparison operator is null and is not null. For example, if a piece table with its own operator is used, and the result depends on the null value, the final result must be null. In the WHERE clause, if Oracle determines that the result is null, the return value is false. The select clause with this clause is not returned, no error message is returned.

---- For example, query the row where MGR is null 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-81  5000        10

---- The write Method for 1st and 2 sentences is incorrect. If the WHERE clause fails, the return result is null. If the third sentence is correct, return the row with the Mgr null value.

---- 3. null value and operator

---- 1. null value and delimiter

---- Series Operator

---- Table reaching

---- Result

ANDNULL AND TRUENULLNULL AND FALSEFALSENULL AND NULLNULLORNULL OR TRUETRUENULL OR FALSENULLNULL OR NULLNULLNOTNOT NULLNULL

---- It can be seen that in the true value table, except for null and false, if the result is false, null or true, if the result is true, the result is null.

---- Although in the WHERE clause, if Oracle determines that the WHERE clause whose result is null is false, null is different from false in the partition table. For example, in not (null and false) and not (null and null), there is only one partition between false and true, but not (null and false) is true, and not (null and null) is null.

---- The following example shows how to use the null value and the Operator:

SQL > select * from emp where not comm=null and comm!=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 clause. The clause "not comm = NULL and comm! = 0 "equal price in null and comm! = 0. For any row, if comm is a number value not equal to 0, the equal price of a piece is null and true, and the result is null. If comm is equal to 0, the equal price of a piece is null and false, and the result is false. The final result is not returned.

---- The clause of the second select clause is the "not" (not) of the first select clause, for example, if comm is a number value not equal to 0, the same price of a piece is not null, and the result is null. If comm is equal to 0, the same price of a piece is not false, the result is true. The final result returned to the row where comm is equal to 0.

---- 2. Comparison between null values and operators

---- (1) is [not] Null: it is the only operator used to test the null value (see "null value test ").

（2）=、!=、>=、<=、>、<SQL >select ename,sal,comm from emp where sal >comm;ENAME            SAL      COMM---------- --------- ---------ALLEN           1600       300WARD            1250       500TURNER          1500         0

---- If Sal or comm is a row with null values, SAL> the result of comm ratio is null, and no rows with Sal or comm null values are returned.

---- (3) Operator in and not in

SQL >select ename,mgr from emp where mgr in (7902,NULL);ENAME            MGR---------- ---------SMITH           7902

---- In the preceding sentence, the equal price of items "Mgr in (7902, null)" is higher than Mgr = 7902 or Mgr = NULL. For any row in Table EMP, if MGR is null, the equal price of the preceding item is null or null, that is, null; if MGR is not equal to 7902, the equal price of the above item is false or null, that is, null; If MGR is equal to 7902, the equal price of the preceding item is true or null, that is, true. Therefore, the final result can be returned to rows equal to 7902 such as MGR.

SQL >select deptno from emp where deptno not in ('10',NULL);no rows selected

---- In the above sentence, the price of a piece "deptno not in ('10', null)" is equal to deptno! = '10' and deptno! = NULL: For any row in the EMP table, the result of a piece can only be null or false, so that the row is not returned.

---- (4) Any, some

SQL >select ename,sal from emp where sal > any(3000,null);ENAME            SAL---------- ---------KING            5000

---- "SAL> Any (3000, null)" is equal to Sal> 3000 or SAL> null. Like the previous statement (3), the final result is returned to all rows with SAL> 3000.

---- (5) All

SQL >select ename,sal from emp where sal > all(3000,null);no rows selected

---- "SAL> All (3000, null)" is equal to Sal> 3000 and Sal> null. The result can only be null or false and is not returned.

---- (6) (not)

SQL >select ename,sal from emp where sal between null and 3000;no rows selected

---- The equal price of the piece "Sal between null and 3000" is equal to Sal> = NULL and Sal <= 3000. The result can only be null or false, and the result is not returned.

SQL >select ename,sal from emp where sal not between null and 3000;ENAME            SAL---------- ---------KING            5000

---- Item "Sal not between null and 3000" is equal to Sal 3000, similar to the first sentence of the preceding statement (3), return the row SAL> 3000.

---- The table below is a smaller knot than the operator and null value:

---- Operator

---- Table dashboard (for example, A and B are null and c = 10)

---- Result

Is null, is not nulla is nulltruea is not nullfalsec is not nulltrue = ,! =, >=, <=,>, <A = nullnulla> nullnullc = nullnullc> nullnullin (= any) A in (10, null) nullc in (10, null) truec in (20, null) nullnot in (equal price on! = All) A not in (20, null) nullc not in (20, null) falsec not in (10, null) nullany, somea> Any (5, null) nullc> Any (5, null) truec> Any (15, null) nullalla> All (5, null) nullc> All (5, null) nullc> All (15, null) false (not) betweena between 5 and nullnullc between 5 and nullnullc between 15 and nullfalsea not between 5 and nullnullc not between 5 and nullnullc not between 15 and nulltrue

---- 3. null value and operator

---- (1) Operator: NULL values are not equal to 0, and any computation table containing null values is empty, for example, if the value of null plus 10 is null.

---- (2) operator |: because the method for processing zero character values before the Oracle object is the same as the method for handling null values (it is still true in the later version), the bitwise | and null values are equal to zero characters. 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      1900WARD            7698 WARD7698           1250       500      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      1500ADAMS           7788 ADAMS7788          1100 JAMES           7698 JAMES7698           950 FORD            7566 FORD7566           3000 MILLER          7782 MILLER7782         1300 

---- We can see that if MGR is an empty value, ename | MGR is the result of ename; If comm is a blank value, sal + comm are empty values.

---- IV. null value and number of functions

---- 1. Empty value and measure Letter Number

---- For the degree-level correspondence, if the parameter value is null, the return value (except nvl and translate) is null. For example, ABS (Comm) in the following example, if comm is an empty value, ABS (Comm) is an empty value.

SQL > select ename,sal,comm,abs(comm) from emp where sal< 1500;ENAME            SAL      COMM ABS(COMM)---------- --------- --------- ---------SMITH            800WARD            1250       500       500MARTIN          1250      1400      1400ADAMS           1100JAMES            950MILLER          1300

---- 2. Empty value and group letter count

---- The number of group functions is slightly empty. In practice, the root data needs to be able to use nvl to replace null values with zero. Example:

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 clause is a row with an empty comm value. The second select clause uses nvl to calculate all the comm values, the numbers and flat average values of all units are different. In addition, it should be noted that when the group function is used for data processing, different writing methods may have different meanings, in practice, the app shall work in the palm of the hand. For example:

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

---- We can see sum (SAL + comm), sum (SAL) + sum (Comm), sum (nvl (SAL, 0) + nvl (Comm, 0 )) partition: sum (SAL + comm) calculates the sum of the rows after adding them first. If one of Sal and comm is null, the row is not counted at all; sum (SAL) + sum (Comm) is the sum of the first calculated rows and then the sum, the null values in Sal and comm are ignored, but one of the results of sum (SAL) and sum (Comm) is null, the sum of the two is null. In sum (nvl (SAL, 0) + nvl (Comm, 0), the null values in Sal and comm are treated as 0.

---- 5. uniqueness of null values

---- 1. What is the value of the null value greater than the value in the sorting order. For example:

SQL > select ename,comm from emp where deptno='30' order by comm；ENAME           COMM---------- ---------TURNER             0ALLEN            300WARD             500MARTIN          1400BLAKE JAMES

---- 2. null values cannot be cited. Although a cable reference is created on a column, it is used to query the null values of the column because the null values are not indexed, the efficiency of the inquiry cannot be improved. 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

---- The other reason is that a null value is not cited. Therefore, you can create a 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.

Author's blog:Http://blog.csdn.net/chensheng913/