Meaning explanation:
Q: What is NULL?
Answer: When we don't know what data there is, that is unknown, NULL can be used. We call it empty. In ORACLE, the length of a table column with a null value is zero.
ORACLE allows fields of any data type to be empty, except for the following two cases:
1, the primary key field (primary key),
2. Fields with NOT NULL restriction conditions have been added during definition
instruction:
1. It is equivalent to having no value and being an unknown number.
2. NULL is different from 0, empty string, and space.
3. Add, subtract, multiply, divide and other operations on the empty value, the result is still empty.
4. The processing of NULL uses the NVL function.
5. When comparing, use keywords "is null" and "is not null".
6. Null values cannot be indexed, so some qualified data may not be found when querying. In count(*), use nvl(column name, 0) to check again.
7. When sorting, it is larger than other data (the index is sorted in descending order by default, small → large), so the NULL value is always ranked last.
Instructions:
SQL> select 1 from dual where null=null;
No record found
SQL> select 1 from dual where null='';
No record found
SQL> select 1 from dual where ``='';
No record found
SQL> select 1 from dual where null is null;
1
---------
1
SQL> select 1 from dual where'' is null;
1
---------
1
Self-test example:
1. SELECT COUNT(*) FROM DUAL WHERE'' = NULL; - count(*)=0
2. SELECT COUNT(*) FROM DUAL WHERE'' IS NULL; - count(*)=1
3. SELECT COUNT(*) FROM DUAL WHERE'' =''; --count(*)=0
4. SELECT COUNT(*) FROM DUAL WHERE'''' =''''; ----count(*)=1
5. SELECT COUNT(*) FROM DUAL WHERE NULL IS NULL;-- count(*)=1
Therefore, the empty string ’’ is the representation format of the NULL character type.
Some people may think that NULL is NULL, and there is no type in itself, but I think there are types of NULL, but different types of NULL are represented by the same keyword NULL. Moreover, NULL itself can be converted into any type of data, so it feels like NULL has no data type.
It is easy to prove that an empty string is NULL:
SQL> SELECT 1 FROM DUAL WHERE'' ='';
No rows selected
SQL> SELECT 1 FROM DUAL WHERE'' IS NULL;
1
----------
1
SQL> SELECT DUMP(''), DUMP(NULL) FROM DUAL;
DUMP DUMP
---- ----
NULL NULL
Any one of the above three SQL statements is sufficient to prove that the empty string ‘’ is NULL.
Some people may say, since ‘’is NULL, why can’t the judgment of IS ’’ be made?
SQL> SELECT 1 FROM DUAL WHERE `` IS'';
SELECT 1 FROM DUAL WHERE'' IS''
*There is an error in line 1:
ORA-00908: missing NULL keyword
In fact, you can see the answer from the above error message. The reason is that IS NULL is Oracle's grammar. It is NULL when Oracle is running. But now that Oracle has not run this SQL, it is blocked by the SQL parser due to incorrect grammar. Oracle’s grammar does not include the wording of IS ’’, so this cannot be called the reason that ‘’ is not NULL.
So why should I say that ‘’is the character representation of NULL? Because ‘’ and NULL are not exactly the same. For NULL, it represents the NULL value of various data types. As for the empty string ’’, although it also has the characteristic of NULL that can be converted into any other data type, it shows the characteristics of the character type both in form and in essence.
Share on:
