Usage of null 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. When sorting is larger than other data, indexes are 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 --------- 1SQL> 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 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 sellnumber (200001) -- monthly sales amount create table sale (month char (6), month number); insert into sale values ('123 ', 1000); insert into sale values ('20140901', 200002); insert into sale Values ('20140901', 200003); insert into sale values ('20160901', 1200); insert into sale values ('20160901', 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 entered. SQL> select * from sale where region like '%'; MONTH limit ------ --------- 200001 1000200002 1100200003 1200200004 1300200005 1400200006 1500200007 1600200101 1100200202 1200200301 1300200008 1000 11 records are queried. 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 1000200002 1100200003 1200200004 1300200005 1400200006 1500200007 1600200101 1100200202 1200200301 1300200008 1000200009 Query 12 records. oracle null is used in this way. We 'd better be familiar with its conventions to prevent incorrect results.