Detail NULL values in Oracle

1. What is NULL?
Null represents Unknow (unknown), which does not represent any value. For example, a column in a row has no value and is null.
Oracle allows fields of any one data type to be empty, except for the following two scenarios:
1) Primary key field (primary key),
2) A field with a NOT NULL constraint defined


2. What's the use of NULL?
1) null can be used for conditional judgment:
SELECT * from EMP WHERE COMM is NULL;
Or
SELECT * from EMP WHERE COMM are not NULL;


3. What are the common precautions for using null?
1) to determine if a column or variable is NULL, use only the condition is null or is not NULL
2) null value cannot be indexed
3) NULL is handled by default for the maximum value in the sort operation:
SELECT * from EMP ORDER by Comm DESC;
4) Null in Oracle and any value arithmetic operation result is null
#加减乘除操作:
SELECT 1+null from DUAL;
SELECT 1-null from DUAL;
SELECT 1*null from DUAL;
SELECT 1/null FORM DUAL;


5) null cannot be used with comparison operators such as =, <>! =, consider how the following block of statements output?
DECLARE
N1 VARCHAR2 (20);
N2 VARCHAR2 (): = ' a ';
BEGIN
IF N1 <> N2 then--equivalent to NULL
Dbms_output.put_line (' <> ');
ELSE
Dbms_output.put_line (' = ');
END IF;
END;


Output: =


6) Work, especially in the development of reports. Use the NVL function to ignore null values in SQL statements involving quantity, amount statistics, and so on.


4. Introduction to related NULL function usage:
1) NVL (P1,P2) function, returns P2 if P1 is empty, otherwise returns P1. Refer to the following example for the processing of the above statement block:
DECLARE
N1 VARCHAR2 (20);
N2 VARCHAR2 (): = ' a ';
BEGIN
IF NVL (N1, ' xx ') <> N2 Then
Dbms_output.put_line (' <> ');
ELSE
Dbms_output.put_line (' = ');
END IF;
END;


2) NVL2 (P1,P2,P3) function, returns P2 if P1 non-empty, otherwise returns P3:
SELECT NVL2 (null,1,2) from DUAL;
SELECT NVL2 (0,1,2) from DUAL;


3) Nullif (P1,P2) function, if P1=P2 returns NULL, otherwise returns P1:
SELECT Nullif from DUAL;
SELECT Nullif from DUAL;


4) COALESCE (P1,P2. Pn) function, return the first non-null value:

SELECT COALESCE (null,null,1,2,3) from DUAL;

-----------------------------------------------

Dylan presents.