Null values in SQL and oracle

The introduction of 1null value NULL is a unique data type in the database. When a column in a record is NULL, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value. This is the origin and basis of NULL. All NULL-related operations

1. Introduction of null value NULL is a unique data type in the database. When a column in a record is NULL, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value. This is the origin and basis of NULL. All NULL-related operations

1 Introduction to null values

NULL is a unique data type in the database. If a column in a record is NULL, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value. This is the origin and basis of NULL. The results of all NULL-related operations can be derived from the concept of NULL.

2 Introduction to null values in oracle

When you do not know the specific data, it is unknown. You can use NULL to call it NULL. in ORACLE, the column length containing NULL values is zero. Fields of any data type are allowed to be empty, except for the following two cases:

A. primary key field (primary key );

B. fields with the NOT NULL restriction added during definition

3 Description of null values in Oracle:

A. It is equivalent to no value and is unknown.

B. NULL and 0, empty strings, and spaces are different.

C. add, subtract, multiply, and divide null values. The result is still null.

D. Use the NVL function for NULL processing.

E. Use the keywords "is null" and "is not null" for comparison ".

F. null values cannot be indexed. Therefore, some qualified data may not be found during query. In count (expr), nvl (column name, 0) is used for processing and then query.

G. The sorting time is larger than other data (the index is sorted in descending order by default, small → large), so the NULL value is always at the end.

Is null and is not null are an integral whole. Changing to IS or is not is incorrect. We can see the difference between NULL and NULL strings from the above.

Any comparison operation with NULL, such as <>, =, <=, and so on, returns the UNKNOWN (here, the unknown is null, and it is used separately similar to the Boolean value false ). the judgment and comparison rules are summarized as follows:

Summary table of judgment and comparison rules

Example: Usage:

SQL> select 1 from dual where null = null;

No records found

SQL> select 1 from dual where null = "';

No records found

SQL> select 1 from dual where ''= '';

No records found

SQL> select 1 from dual where null is null;

1

---------

1

SQL> select 1 from dual where nvl (null, 0) = nvl (null, 0 );

1

---------

1

4. null performs some arithmetic operations, such as +,-, *,/, and so on. The result is null, but the join operator |, null is ignored. The concat function also ignores null.

SQL> select null | 'abc' from dual;

NUL

---

Abc

SQL> select concat (null, 'abc') from dual;

CON

---

Abc

SQL> select null + 10 from dual;

NULL + 10

----------

5. null-related function rules

Oracle has functions such as nvl, nvl2, nullif, and coalesce to handle null.

5.1 nvl (expr1, expr2)

Description: If expr1 is null, expr2 is used as the return value. If it is not null, expr1.expr1 and expr2 are returned. If the type is different, automatic conversion is used. If the conversion fails, an error is returned.

In SQL * PLUS, values are right aligned and characters are left aligned. The third statement shows that null and ''are different.

SQL> select nvl (null, 0) from dual;

NVL (NULL, 0)

-----------

0

SQL> select nvl (to_char (null), 0) from dual;

N

-

0

SQL> select nvl ('', 0) from dual;

N

-

0

5.2 nvl2 Functions

Syntax: nvl2 (expr1, expr2, expr3)

Description: expr1. If it is null, expr3 is returned; otherwise, expr2 is returned.

The expr2 and expr3 types are different. The expr3 type is converted to the expr2 type.

SQL> select nvl2 (null, '1', 2) from dual;

N

-

2

Expr2 is null, And the return value type is the same as expr3.

SQL> select nvl2 (null, null, 2) from dual;

NVL2 (NULL, NULL, 2)

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

2

SQL> select nvl2 (null, null, '2') from dual;

N

-

2

If the automatic conversion fails for different types, an error is returned.

SQL> select nvl2 (null, 1, 'B') from dual;

Select nvl2 (null, 1, 'B') from dual

*

ERROR at line 1:

ORA-01722: invalid number (invalid number)

5.3 nullif Function

Syntax: nullif (expr1, expr2)

Description: determines whether expr1 and expr2 are equal. If they are equal, null is returned. Otherwise, expr1. expr1 and expr2 must be of the same type.

SQL> select nullif (1, 3) from dual;

NULLIF (1, 3)

-----------

1

SQL> select nullif (1, 1) from dual;

NULLIF (1, 1)

-----------

SQL> select nullif ('1', 1) from dual;

Select nullif ('1', 1) from dual

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

SQL> select nullif ('AB', 'AB') from dual;

NU

--

SQL> select nullif (null, 1) from dual;

Select nullif (null, 1) from dual

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected-got CHAR

SQL> select nullif (to_char (null), '1') from dual;

N

-

5.4 coalesce Functions

Syntax: coalesce (expr1, expr2 ,..., Exprn)

Description: returns the first non-null value from left to right. If all list elements are null, null is returned. All must be of the same type.

SQL> select coalesce (null, null, null) from dual;

C

-

SQL> select coalesce (null, 1, 2) from dual;

COALESCE (NULL, 1, 2)

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

1

SQL> select coalesce (1, '1', 1) from dual;

Select coalesce (1, '1', 1) from dual

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

6 null and Index

B * Tree indexes in Oracle do not store all null columns,