Oracle null value processing

1. null Concept

   There is an important concept in the database: null. Sometimes, in a table, some field values may be null because the data does not know the value or does not exist at all.

2. null value judgment

   The null value is not equal to the space in the string or the value of the number type 0. Therefore, =, <> cannot be used to determine whether a field value is null. A special phrase is required: Is
Null is used to select records with null fields. Similarly, records that do not contain null values can be selected using is not null. For example:

Select
* From party where leader is null;

Select
* From party where leader isNot NULL;

3. Convert null values

ORACLE:
Nvl
Nvl function:
The nvl function converts null fields into default fields for output.

Nvl (expr1, expr2)
Expr1, the field name or expression to be converted.
Expr2, null substitution value

The following is an example of number, date, Char or varchar2:
Nvl (commission_pct, 0)
Nvl (hire_date, '01-Jan-97 ')
Nvl (job_id, 'no job yun ')

SQL Server:
Isnull: If it is null, the specified value is returned; otherwise, the original value is returned.
Select username, isnull (address, 'None') from [user]...
------------------------------------------------------------------
Nullif returns NULL when two values (or expressions) are equal. Otherwise, the first parameter is returned.
Select sum (nullif (price, 100) from...
If the price is 100, it is treated as null, sum () will ignore the null value, so...
----------------------------------------------------------------------
Coalesce returns the first non-null value. The parameter value is any number.
Select coalesce (homephone, officephone, mobile) from ....
If a phone number is entered, a value is returned.
-----------------------------------------------------------------------
Access:
NZ
Same as SQL Server isnull. It can only be used in access programs, not supported by jet ado
Select username, NZ (address, 'None') from [user]...
IIF
Select username, IIF (address is null, 'none', address) from [user]...

Select username, IIF (address is null, 'unwrite',' written ') from [user]...