NULL use details
Software Environment:
1, Windows NT4.0 Oracle 8.0.4
2, the Oracle installation path is: C: / ORANT
Meaning explanation:
Q: What is NULL?
A: When we don't know what data, it is not known, you can use null, we call it empty, oracle, the length of the null value is zero.
Oracle allows any field of data types to be empty, except for both cases:
1, primary keyfield (Primary key),
2, the field of NOT NULL limit conditions has been added when defined
Description:
1. Isometrical no value, is an unknown.
2, NULL and 0, empty strings, spaces are different.
3. Do add, reduce, multiply, divide the empty value, and the result is still empty.
4, NULL's processing uses NVL functions.
5. Use the keywords with "is null" and "is not null" when comparing.
6, null value cannot be indexed, so some in line with the conditions may not be found, in count (*), use NVL (column name, 0) to check.
7. Sort is larger than other data (index default is descending order, small → big), so NULL value is always in the end.
How to use: SQL> SELECT 1 from dual where null = null;
No record
SQL> SELECT 1 from dual where null = '';
No record
SQL> SELECT 1 from dual where '' = '';
No record
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
Declaration, minus, multiply, divided operations for null values, and 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 a record.
Note: This record is the NULL in the SQL statement.
Set some listed as null value
Update table1 set column 1 = NULL WHERE list 1 is not null;
There is a product sales table Sale, the table structure is:
Month Char (6) - Month
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: This recorded SELL value is empty)
COMMIT;
12 records
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 11 records.
Result Description:
Query results show that this SQL statement query does not list the value of NULL
At this time, the field is required to be 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 12 records.
Oracle's null value is such a usage, we are best to be familiar with its conventions to prevent the results of the results are incorrect.
