The difference between the Null and 0 in Oracle-CodePudding

Null said no value, empty
0 means has practical value of the existence of value is 0

For example: query no bonus employees

The Select empno eename, comm
The from emp
Where comm=0 or comm is null

CodePudding user response:

Thanks for sharing!

CodePudding user response:

Plus a supplement, once I met such a problem, from SQL server database to oracle, the (all poker directly with transparent gateway that data transfer), SQL server empty (NULL) to oracle, NULL in oracle database into a NULL character (asc the character code of 0), the query within oracle NULL character, can't copy and paste, cannot use is NULL judgments, sift through such data available function ASCII (field name)=0

CodePudding user response:

refer to the second floor Wazy_csdn response:

and fill a, once I met such a problem, from SQL server database to oracle, the (all poker directly with transparent gateway that data transfer), SQL server empty (NULL) to oracle, NULL in oracle database into a NULL character (asc the character code of 0), the query within oracle NULL character, can't copy and paste, cannot use is NULL, the screening of such data available function ASCII (field name)=0

Oracle inside of the empty string is equivalent to a 'and empty NULL can write=' conditions, can also write is NULL, many database both is different

CodePudding user response:

Evil NULL, can cause a lot of problem, performance issues, logic problems, try to make the system of the NULL value less, if there is inevitable, as in other fields to evade,
As in the process of examination and approval of the workflow, it is best not to use the examination and approval opinions whether specific node to NULL judgment, but with the specific field,

CodePudding user response:

Plus a supplement, once I met such a problem, from SQL server database to oracle, the (all poker directly with transparent gateway that data transfer), SQL server empty (NULL) to oracle, NULL in oracle database into a NULL character (asc the character code of 0), the query within oracle NULL character, can't copy and paste, cannot use is NULL, the screening of such data available function ASCII (field name)=0

reference minsic78 reply: 3/f

Quote: refer to the second floor Wazy_csdn response:

Plus a supplement, once I met such a problem, from SQL server database to oracle, the (all poker directly with transparent gateway that data transfer), SQL server empty (NULL) to oracle, NULL in oracle database into a NULL character (asc the character code of 0), the query within oracle NULL character, can't copy and paste, cannot use is NULL, the screening of such data available function ASCII (field name)=0

Oracle inside of the empty string is equivalent to a 'and empty NULL can write=' conditions, can also write is NULL, a lot of different database both

Is not equivalent to oh, an empty string ASCII is 0, but the null ASCII is null, the empty string by asc code can be seen and null is two look the same data, but in fact is not the same data, and an empty string is inserted into the oracle general deposit is null, not empty string, the input method can't play out, I the encounter to the empty string data because the transparent gateway problem of oracle SQL server, insert a null to an empty string to the table fields of oracle, I tried many times, can't use='or is null, but can use ASCII (field name)=0 to query

CodePudding user response:

 SQL> Drop table test purge; 

Table dropped. 

SQL> Create table test (x varchar2 (10)); 

The Table created. 

SQL> Insert into test values (null); 

1 row created. 

SQL> Insert into test values ("); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> Select * from the test; 

X 
-- -- -- -- -- -- -- -- -- -- 



SQL> Select count (*) from the test. 

COUNT (*) 
-- -- -- -- -- -- -- -- -- -- 
2 

SQL> Select count (*) from the test where x is null. 

COUNT (*) 
-- -- -- -- -- -- -- -- -- -- 
2 

SQL> Select count (*) from the test where x=' '; 

COUNT (*) 
-- -- -- -- -- -- -- -- -- -- 
0 

SQL> Select the dump (x) from the test; 

The DUMP (X) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
NULL 
NULL 

SQL> Insert into test values (' 111 '); 

1 row created. 

SQL> Update the test set x='where x=' 111 '; 

1 row updated. 

SQL> commit; 

Commit complete. 

SQL> Select the dump (x) from the test; 

The DUMP (X) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
NULL 
NULL 
NULL 

SQL>

I said above is a little problem: in the where clause for x='this kind of writing and is null inequitable,
But the insert and update using 'empty string that represents the null