Null in Oracle

When we don't know what the data is, it is unknown, it can be null, we call it null, and in Oracle, the table column with null values is zero in length.

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

Description
1, is equivalent to no value, is unknown, null and 0, empty string, space are different.
Sql> SELECT 1 from dual WHERE ' = ';
No rows selected
Sql> Select 1 from dual where ' =null;
No rows selected
Sql> SELECT 1 from dual WHERE null = NULL;
No rows selected
Sql> Select 1 from dual where null = 0;
No rows selected
Sql> Select 1 from dual where "is null;
1
----------
1
Sql> SELECT 1 from dual WHERE null is null;
1
----------
1
2, add, subtract, multiply and divide the null value, and the result is still empty. If this comparison occurs in the Where condition, it is unknown, equal to false, and thus does not return data
Sql> Select 1 from dual where (select 1+null from dual) is null
Sql> Select 1 from dual where (select 1-null from dual) is null
Sql> Select 1 from dual where (select 1*null from dual) is null
Sql> Select 1 from dual where (select 1/null from dual) is null

Note: The case of not in
Sql> Select 1 from dual where ' B ' isn't in (' a ', null)
Sql> Select 1 from dual where ' b '! = ' a ' and ' B '!=null
The above two sentences are equivalent because the ' B '!=null always returns NULL, so the truth expression is always not true, and no data is returned.
--The correct approach should be to exclude null, for example, to exclude the occurrence of null values in parentheses
3, NULL processing uses the NVL function.
Sql> Select 1 from dual where NVL (null,0) =NVL (null,0);
4. Use the keyword "is null" and "is not NULL" when comparing.
Sql> Select 1 from dual where "is null;
Sql> Select 1 from dual where "is not null;
5, the null value cannot be indexed, so the query when some eligible data may not be found, COUNT (*), with NVL (column name, 0) processing and then check.
？？？？
6. Sorting
1) without "taking care" of the case, we can think of those null values as the value is the largest in all content, so the ascending sort after the null value at the end, the reverse sort after the null value at the top!
2) in the case of special "care", when the "NULLS first" is specified, the null value is arranged at the top, either in ascending or descending order, and when "NULLS last" is specified, the null value is arranged in the last face, either in ascending or descending order.

7. Most aggregate functions ignore NULL, for example
1) avg,1000, NULL, NULL, 2000, averaging using AVG, is actually (1000+2000)/2, so in special cases it is best to take and then divide the number of result sets
2) count,1000, NULL, NULL, 2000, the result is 2, preferably by using count (1) or COUNT (*)

Null in Oracle