Usage of null in Oracle

It is best not to look for NULL, isnotnull, isnull, or null values. What is NULL? A: When we do not know the specific data, it is also unknown,

It is best not to look for NULL, or is not null, is null, and cannot compare null values. Q: What is NULL? A: When we do not know the specific data, it is also unknown,

It is best not to look for NULL, or is not null, is null, and cannot compare null values.

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. fields with the not null restriction added during definition

Note:
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 (*), use nvl (column name, 0) for processing and then query.
7. Sorting is larger than other data (indexes are sorted in descending order by default, small → large ),
Therefore, 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 = '';
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
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 value in the SQL statement.
Set null values for some columns
Update table1 set column 1 = NULL where column 1 is not null;
There is a sales table sale with the following structure:
Month char (6) -- month
Sellnumber () -- monthly sales amount
Create table sale (month char (6), Region number );
Insert into sale values ('20140901', 200001 );
Insert into sale values ('20140901', 200002 );
Insert into sale values ('20140901', 200003 );
Insert into sale values ('20140901', 200004 );
Insert into sale values ('20140901', 200005 );
Insert into sale values ('20140901', 200006 );
Insert into sale values ('20140901', 200007 );
Insert into sale values ('20140901', 200101 );
Insert into sale values ('20140901', 200202 );
Insert into sale values ('20140901', 200301 );
Insert into sale values ('20140901', 200008 );
Insert into sale (month) values ('20140901 ');
(Note: The limit value of this record is null)
Commit;
12 records in total
SQL> select * from sale where region like '% ';
MONTH week
---------------
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
200008 1000

11 records found.

Result description:
The query results indicate that this SQL statement does not query fields 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 (latency, 0) like '% ';

MONTH week
---------------
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 are queried.

Oracle null is used in this way. We 'd better familiarize ourselves with its conventions to prevent incorrect results.

Other comments:

In the ms SQL Server, it exists.
That IS, WHERE field_1 is null and filed_1 = ''. Two results are returned.
''And NULL are two things.

After you store ''in Oracle 8.1.6 and 9.2, the database is automatically stored as NULL.
In this case, you can only use WHERE field_1 IS NULL
It seems that there is no such thing as ''or NULL in ORACLE.

It can be understood as follows:

To differentiate data types,
The NULL String ''must be NULL.
The NULL number is also NULL.
Other NULL data types are also NULL.

If the data type is not differentiated,
NULL String ''is NULL, NULL is NULL''