Difference between Null and Null String ''in Oracle

The difference between Null and NULL String ''in Oracle: Q: What is Null? A: When we do not know the specific data, it is also unknown. We can use NULL. We call it NULL. in ORACLE, the column length containing 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. Description of fields with the not null restriction added during definition: 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 (*), nvl (column name, 0) is used for processing and then query. 7. Sorting 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. Usage: SQL> select 1 from dual where null = null; no records found SQL> select 1 from dual where null = ''; SQL> select 1 from dual where ''=''; 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, and 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 found. note: This record is the null setting in the SQL statement. Some columns are NULL values. update table1 set column 1 = null where column 1 is not null. an existing product sales table sale has the following table structure: month char (6) -- month sale number (200001) -- monthly sales amount create table sale (month char (6), sale number); insert into sale values ('123 ', 1000); insert into sale values ('20140901', 200002 ); Insert into sale values ('20170101', 200003); insert into sale values ('20170101', 1200); insert into sale values ('20170101', 200004 ); insert into sale values ('20170101', 200006); insert into sale values ('20170101', 1500); insert into sale values ('20170101', 200007 ); insert into sale values ('20170101', 200202); insert into sale values ('20170101', 1200); insert into sale values ('20170101', 200301 ); insert into sale (month) values ('20140901 '); (Note: the limit value of this record is null) commit; a total of 12 records are input SQL> select * from sale where limit like '% '; MONTH success ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 Query 11 records. result Description: the query results indicate that this SQL statement does not query a field with a column value of NULL. In this case, the field must be NULL. SQL> select * from sale where region like '%' or region is null; SQL> select * from sale where nvl (Region, 0) like '% '; MONTH success ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009 Query 12 records. oracle null is used in this way. We 'd better familiarize ourselves with its conventions to prevent incorrect results. But is there a difference between null and null strings in the char and varchar2 database fields? Perform 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', ''); -- according to the above explanation, SQL> insert into test (a) values ('4') with values in field B '); SQL> select * from test; a B ---------- 1 1 2 2 2 3 4 SQL> select * from test where B = ''; ---- according to the above explanation, there should be A record, but in fact there is no record of unselected rows SQL> select * from test where B is Null; ---- according to the above explanation, 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 = ''; SQL> select * from test where B is null; a B ---------- 2 3 4 test Result description. For the char and varchar2 fields, ''is null; however, the ''after the where condition is not null. The same applies to default values!