Oracle NULL values

Oracle NULL values

1. What is NULL?
NULL indicates UNKNOW (unknown), which does not represent any value. For example, if a column in a row does not have any value, it is NULL.
Oracle allows blank fields of any data type, except for the following two cases:
1) primary key field (primary key ),
2) fields with the not null restriction added during definition

2. What is the use of NULL?
1) NULL can be used for condition determination:
SELECT * from emp where comm is null;
Or
SELECT * from emp where comm is not null;

3. What are precautions for using NULL?
1) Determine whether the column or variable is null. Only the condition is null or is not null can be used.
2) null values cannot be indexed
3) NULL is processed by the maximum value by default in the sorting operation:
SELECT * from emp order by comm DESC;
4) ORACLE's NULL and any value arithmetic operations result in NULL
# Addition, subtraction, multiplication, division:
SELECT 1 + null from dual;
SELECT 1-null from dual;
SELECT 1 * null from dual;
SELECT 1/null form dual;

5) NULL cannot use comparison operators such as =, <>! =. How can I output the following statement blocks?
DECLARE
N1 VARCHAR2 (20 );
N2 VARCHAR2 (20): = '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 report development. In SQL statements involving quantity and amount statistics, the NVL function is used to ignore NULL values.

4. Usage of related NULL functions:
1) NVL (p1, p2) function. If p1 is null, p2 is returned; otherwise, p1 is returned. For the processing of the preceding statement block, see the following example:
DECLARE
N1 VARCHAR2 (20 );
N2 VARCHAR2 (20): = '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. If p1 is not null, p2 is returned; otherwise, p3 is returned:
SELECT NVL2 (NULL, 1, 2) from dual;
SELECT NVL2 (0, 1, 2) from dual;

3) NULLIF (p1, p2) function. If p1 = p2, NULL is returned; otherwise, p1 is returned:
Select nullif (1, 1) from dual;
Select nullif (1, 2) from dual;

4) COALESCE (p1, p2.. Pn) function, returns the first non-null value:
 
Select coalesce (NULL, NULL, 1, 2, 3) from dual;

Oracle uses order by to sort NULL values

NULL Value and NULL String in DB2

Oracle Index and NULL

Difference between NULL and NULL String ''in Oracle