Use of null in Oracle databases

Are you very worried about the actual Oracle database null operations? If this is the case, the following articles will give you corresponding solutions. The following articles mainly introduce the specific use of null in Oracle databases, the following is a detailed description of the relevant content.

Q: What is NULL?

A: When we do not know the specific data, it is also unknown. NULL can be used,

We call it null. In an Oracle database, the length of a table with null values is zero.

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

Note:

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

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

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

4. The NVL function is used for NULL processing.

5. Use the keyword "is null" and "is not null" for comparison ".

6. null values cannot be indexed. Therefore, some data that meets the query conditions may not be found,

In count *), use nvl column name, 0) to process and then query.

7. When sorting data is larger than other data, indexes are sorted in descending order by default, small → large ),

Therefore, the NULL value is always at the end.

Usage:

 
 

  
  
SQL> select 1 from dual where nullnull=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
 

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

 
 

  
  
SQL> select 1+null from dual;  
  
  
SQL> select 1-null from dual;  
  
  
SQL> select 1*null from dual;  
  
  
SQL> select 1/null from dual; 
 
 

A record is queried.

Note: This record is the null value in the SQL statement.

Set null values for some columns

Update table1 set column 1 = NULL where column 1 is not null;

There is a sales table sale with the following structure:

Month char6) -- month

Sellnumber10, 2) -- monthly sales amount

 
 

  
  
create table sale (month char(6),sell number);  
  
  
insert into sale values('200001',1000);  
  
  
insert into sale values('200002',1100);  
  
  
insert into sale values('200003',1200);  
  
  
insert into sale values('200004',1300);  
  
  
insert into sale values('200005',1400);  
  
  
insert into sale values('200006',1500);  
  
  
insert into sale values('200007',1600);  
  
  
insert into sale values('200101',1100);  
  
  
insert into sale values('200202',1200);  
  
  
insert into sale values('200301',1300);  
  
  
insert into sale values('200008',1000);  
  
  
insert into sale(month) values('200009'); 
 
 

Note: The limit value of this record is null.

 
 

  
  
commit; 
 
 

12 records in total

 
 

  
  
SQL> select * from sale where sell like '%';  
  
  
MONTH SELL  
  
  
------ ---------  
  
  
200001 1000  
  
  
200002 1100  
  
  
200003 1200  
  
  
200004 1300  
  
  
200005 1400  
  
  
200006 1500  
  
  
200007 1600  
  
  
200101 1100  
  
  
200202 1200  
  
  
200301 1300  
  
  
200008 1000 
 
 

11 records are queried.

Result description:

The query results indicate that this SQL statement does not query fields with a column value of NULL.

In this case, the field must be NULL.

 
 

  
  
SQL> select * from sale where sell like '%' or sell is null;  
  
  
SQL> select * from sale where nvl(sell,0) like '%';  
  
  
MONTH SELL  
  
  
------ ---------  
  
  
200001 1000  
  
  
200002 1100  
  
  
200003 1200  
  
  
200004 1300  
  
  
200005 1400  
  
  
200006 1500  
  
  
200007 1600  
  
  
200101 1100  
  
  
200202 1200  
  
  
200301 1300  
  
  
200008 1000  
  
  
200009 
 
 

12 records are queried.

Oracle Database null is used in this way. We 'd better familiarize ourselves with its conventions to prevent incorrect results.