The difference between null and empty string ' in Oracle

Meaning explanation:
Q: What is NULL?
A: When we do not know what the specific data, it is unknown, you can use NULL, we call it empty, Oracle, contains null values of the table column length is zero.
Oracle allows fields of any one data type to be empty, except for the following two scenarios:
1, primary key field (primary key),
2. A field with a NOT NULL constraint is defined
Description
1, is equivalent to no value, is unknown.
2, null and 0, empty string, space are different.
3, add, subtract, multiply and divide the null value, and the result is still empty.
4, NULL processing uses the NVL function.
5. Use the keyword "is null" and "is not NULL" when comparing.
6, the null value cannot be indexed, so the query when some eligible data may not be found, COUNT (*), with NVL (column name, 0) processing and then check.
7, the sorting is larger than other data (the index by default is descending, small → large), so the null value is always ranked at the end.
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);

1
---------
1
The null value is added, minus, multiply, divide, and so on, 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;
Existing a Commodity sales table sale, the table structure is:
Month char (6)--months
Sell number (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 of this record is empty)
Commit
Total 12 Records entered
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.
Result Description:
The query results indicate that this SQL statement query does not dequeue a field with a value of NULL
In this case, you need to deal with the field 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
Query to 12 records.
The null value of Oracle is such a usage that we'd better familiarize ourselves with its conventions in case the results are not correct.

But is there a difference between null and an empty string in a database field of char and VARCHAR2 type?
To make a test:
CREATE TABLE Test (a char (5), B char (5));
sql> INSERT INTO Test (A, B) values (' 1 ', ' 1 ');
sql> INSERT INTO Test (A, B) values (' 2 ', ' 2 ');
sql> INSERT INTO Test (A, B) values (' 3 ', ');--as explained above, the
sql> INSERT INTO Test (a) VALUES (' 4 ');
Sql> select * from test;
A B
---------- ----------
1 1
2 2
3
4
Sql> SELECT * FROM test where b= ';----According to the above explanation, there should be a record, but actually no record
Row not selected
Sql> SELECT * FROM test where b is null;----As explained above, there should be a hop record, but there are actually two records.
A B
---------- ----------
3
4
Sql>update table Test set b= ' where a= ' 2 ';
Sql> SELECT * FROM test where b= ';
Row not selected
Sql> SELECT * FROM test where b is null;
A B
---------- ----------
2
3
4
The test results show that "is null for char and VARCHAR2 fields, but not null for where condition."
The same is true for the default value!

The difference between null and empty string ' in Oracle