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.
· Instructions:
· 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
·
· Do add, minus, multiply, divided by empty value, 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)
""
· A total of 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 indicate that this SQL statement query does not list the value of NULL
· In this case, 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 use, we'd better familiarize yourself with its agreement to prevent the results of the results are incorrect.
