Shulou(Shulou.com)05/31 Report--

This article shows you the difference between Null and empty string in Oracle. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Meaning explanation:

Q: what is NULL?

A: when we don't know what the data is, that is, unknown, we can use NULL, we call it empty. In ORACLE, the length of the table column with null values is zero.

ORACLE allows fields of any data type to be empty, except for the following two cases:

1. Primary key field (primary key)

2. Fields that have been defined with NOT NULL restrictions

Description:

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

2. NULL is different from 0, empty string and space.

3. Add, subtract, multiply and divide null values, and the result is still empty.

4. The NVL function is used to deal with NULL.

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

6. Null values cannot be indexed, so some qualified data may not be found in the query. In count (*), use nvl (column name, 0) to process and then check.

7. When sorting, it is larger than other data (the index is sorted in descending order by default, and the small → is large), so the null value is always the last.

How to use it:

SQL > select 1 from dual where null=null

No records were found.

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

No records were found.

SQL > select 1 from dual where''

No records were found.

SQL > select 1 from dual where null is null

one

-

one

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

one

-

one

Do addition, subtraction, multiplication, division and other operations on the null value, 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

A record was found.

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 a merchandise sales table sale, which has the following structure:

Month char (6)-month

Sell number (10Pol 2)-monthly sales amount

Create table sale (month char (6), sell number)

Insert into sale values ('2000011400)

Insert into sale values ('200002)

Insert into sale values ('200003)

Insert into sale values ('200004June 1300)

Insert into sale values ('200005)

Insert into sale values ('200006)

Insert into sale values ('200007 June 1600)

Insert into sale values ('20010 1100)

Insert into sale values ('200202)

Insert into sale values ('200301 June 1300)

Insert into sale values ('200008 million 1 000)

Insert into sale (month) values ('200009 September'); (note: the sell value of this record is empty)

Commit

Enter 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 were found.

The results show that:

The query results show that this SQL statement cannot query fields with column values of NULL.

At this point, you need to deal with 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

200202 1200

200301 1300

200008 1000

200009

12 records were found.

This is how the null value of Oracle is used, and we'd better familiarize ourselves with its convention in case the result is incorrect.

But is there any difference between null and empty strings in database fields of char and varchar2 types?

Take a test:

Create table test (a char (5), b char (5))

SQL > insert into test (aformab) values ('1pm')

SQL > insert into test (a _ r _ b) values (''2')

SQL > insert into test (a _ r _ b) values ('3i _ r _ b _ r _ r

SQL > insert into test (a) values ('44th')

SQL > select * from test

A B

--

1 1

2 2

three

four

SQL > select * from test where baggage recording records-according to the above explanation, there should be a record, but there is actually no record.

No rows selected

SQL > select * from test where b is null;---- as explained above, there should be an one-hop record, but there are actually two records.

A B

--

three

four

SQL > update table test set breadwinners. 'where axioms'.

SQL > select * from test where baked goods

No rows selected

SQL > select * from test where b is null

A B

--

two

three

four

The above is what is the difference between Null and empty string in Oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.