Use of NULL in Oracle __oracle

Q: What is null.

A: When we do not know what the specific data is, it is unknown, you can use NULL,

We call it null, and in Oracle, the table column length with a null value is zero.

Oracle allows NULL fields for any one data type, except for the following two scenarios:

1, the primary key field (primary key),

2. Fields that have been added not NULL restrictions when defined

Description

1, equivalent to no value, is unknown.

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

3, the null value to do add, subtract, multiply, and other operations, the result is still empty.

4, NULL processing using the NVL function.

5. Use keywords "is null" and "is not NULL" when comparing.

6, null value can not be indexed, so the query when some of the conditions of the data may not be found out,

Count (*), use NVL (column name, 0) to be processed before checking.

7, sorting than other data are large (index default is descending order, small → Large),

So null values are always at the end of the line.

How to use:

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); Got is all the data that's empty and not empty

1

---------

1

The null value is added, subtract, multiply, divide and so on operation, the result is still empty.

Sql> select 1+null from dual;

Sql> select 1-null from dual;

Sql> select 1*null from dual;

Sql> select 1/null from dual;

Query to a record.

Note: This record is the null in the SQL statement

Set some column null values

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

There is an existing sales table sale, the table structure is:

Month char (6)--month

Sellnumber (10,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 Sell value for this record is empty)

Commit

Total Input 12 Records

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

Query to 11 records.

Results show:

Query results indicate that this SQL statement queries a field that does not have the column value null

This should be handled in addition to the case where the field is 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

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

200008 1000

200009

Query to 12 records.

Oracle's null value is such a usage, we'd better familiarize ourselves with its convention in case the results are not correct.