1 Introduction to null values
NULL is a unique data type in the database. If a column in a record is NULL, the value of this column is unknown and uncertain. Since it is unknown, there are several possibilities. Therefore, NULL is not a definite value. This is the origin and basis of NULL. The results of all NULL-related operations can be derived from the concept of NULL.
2 Introduction to null values in oracle
When you do not know the specific data, it is unknown. You can use NULL to call it NULL. in ORACLE, the column length containing NULL values is zero. Fields of any data type are allowed to be empty, except for the following two cases:
A. primary key field (primary key );
B. fields with the NOT NULL restriction added during definition
3 Description of null values in Oracle:
A. It is equivalent to no value and is unknown.
B. NULL and 0, empty strings, and spaces are different.
C. add, subtract, multiply, and divide null values. The result is still null.
D. Use the NVL function for NULL processing.
E. Use the keywords "is null" and "is not null" for comparison ".
F. null values cannot be indexed. Therefore, some qualified data may not be found during query. In count (expr), nvl (column name, 0) is used for processing and then query.
G. The sorting time is larger than other data (the index is sorted in descending order by default, small → large), so the NULL value is always at the end.
Is null and is not null are an integral whole. Changing to IS or is not is incorrect. We can see the difference between NULL and NULL strings from the above.
Any comparison operation with NULL, such as <>, =, <=, and so on, returns the UNKNOWN (here, the unknown is null, and it is used separately similar to the Boolean value false ). the judgment and comparison rules are summarized as follows:
Summary table of judgment and comparison rules
Example: 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
4. null performs some arithmetic operations, such as +,-, *,/, and so on. The result is null, but the join operator |, null is ignored. The concat function also ignores null.
SQL> select null | 'abc' from dual;
NUL
---
Abc
SQL> select concat (null, 'abc') from dual;
CON
---
Abc
SQL> select null + 10 from dual;
NULL + 10
----------
5. null-related function rules
Oracle has functions such as nvl, nvl2, nullif, and coalesce to handle null.
5.1 nvl (expr1, expr2)
Description: If expr1 is null, expr2 is used as the return value. If it is not null, expr1.expr1 and expr2 are returned. If the type is different, automatic conversion is used. If the conversion fails, an error is returned.
In SQL * PLUS, values are right aligned and characters are left aligned. The third statement shows that null and ''are different.
SQL> select nvl (null, 0) from dual;
NVL (NULL, 0)
-----------
0
SQL> select nvl (to_char (null), 0) from dual;
N
-
0
SQL> select nvl ('', 0) from dual;
N
-
0
5.2 nvl2 Functions
Syntax: nvl2 (expr1, expr2, expr3)
Description: expr1. If it is null, expr3 is returned; otherwise, expr2 is returned.
The expr2 and expr3 types are different. The expr3 type is converted to the expr2 type.
SQL> select nvl2 (null, '1', 2) from dual;
N
-
2
Expr2 is null, And the return value type is the same as expr3.
SQL> select nvl2 (null, null, 2) from dual;
NVL2 (NULL, NULL, 2)
-----------------
2
SQL> select nvl2 (null, null, '2') from dual;
N
-
2
If the automatic conversion fails for different types, an error is returned.
SQL> select nvl2 (null, 1, 'B') from dual;
Select nvl2 (null, 1, 'B') from dual
*
ERROR at line 1:
ORA-01722: invalid number (invalid number)
5.3 nullif Function
Syntax: nullif (expr1, expr2)
Description: determines whether expr1 and expr2 are equal. If they are equal, null is returned. Otherwise, expr1. expr1 and expr2 must be of the same type.
SQL> select nullif (1, 3) from dual;
NULLIF (1, 3)
-----------
1
SQL> select nullif (1, 1) from dual;
NULLIF (1, 1)
-----------
SQL> select nullif ('1', 1) from dual;
Select nullif ('1', 1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
SQL> select nullif ('AB', 'AB') from dual;
NU
--
SQL> select nullif (null, 1) from dual;
Select nullif (null, 1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected-got CHAR
SQL> select nullif (to_char (null), '1') from dual;
N
-
5.4 coalesce Functions
Syntax: coalesce (expr1, expr2 ,..., Exprn)
Description: returns the first non-null value from left to right. If all list elements are null, null is returned. All must be of the same type.
SQL> select coalesce (null, null, null) from dual;
C
-
SQL> select coalesce (null, 1, 2) from dual;
COALESCE (NULL, 1, 2)
------------------
1
SQL> select coalesce (1, '1', 1) from dual;
Select coalesce (1, '1', 1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
6 null and Index
B * Tree indexes in Oracle do not store all null columns,
Although a UNIQUE index is created in the table, all null rows can still be inserted, rather than all null duplicate rows cannot be inserted. In UNIQUE constraints, (null, null) and (null, null) are different. Of course, in other cases, such as grouping and set operations, all null values are considered equal.
SQL> create table t (a number, B number );
Table created.
SQL> create unique index idx_t on t (a, B );
Index created.
SQL> insert into t values (null, null );
1 row created.
SQL> insert into t values (1, null );
1 row created.
SQL> insert into t values (null, 1 );
1 row created.
SQL> commit;
SQL> insert into t values (1, null );
Insert into t values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT. IDX_T) violated
7 null sorting
By default, order by is in ascending order (asc). At this time, null is the last column. If descending order is specified, null is the first column and null is considered to be the largest.
However, you can use nulls first and nulls last for adjustment.
SQL> select * from emp
2 order by comm asc;
SQL> select * from emp
2 order by comm desc;
SQL> select * from emp
2 order by comm asc nulls first;
SQL> select * from emp
2 order by comm desc nulls last;
8 Relationship Between null and Performance
Not null constraints: defining constraints requires performance consumption. The test below shows that although the constraints are checked very quickly, they sometimes consume resources, at least in this example, the not null constraint is not required unless necessary.
SQL> set serveroutput on
SQL> declare
2 v_value number not null: = 0;
3 start_time number;
4 end_time number;
5 begin
6 start_time: = DBMS_UTILITY.GET_TIME;
7 FOR I in 0 .. 100000000 LOOP
8 v_value: = I;
9 end LOOP;
10 end_time: = DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE (end_time-start_time );
12 END;
13/
1043
PL/SQL procedure successfully completed.
SQL> declare
2 v_value number;
3 start_time number;
4 end_time number;
5 begin
6 start_time: = DBMS_UTILITY.GET_TIME;
7 FOR I IN 0 .. 100000000 LOOP
8 v_value: = I;
9 end LOOP;
10 end_time: = DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE (end_time-start_time );
12 END;
13/
767
9 bind variables and null in dynamic statements
Dynamic SQL and dynamic PL/SQL often use bind variables in PL/SQL. This Bind Variable requires that null values cannot be input directly, in PL/SQL, dynamic statements require that the input Bind Variable must be of the SQL type, while the literal value null is of the non-type, and the null literal value cannot be passed in. Of course, you can use multiple methods. If null is required, you can change null to a null string, TO_NUMBER, TO_CHAR, TO_DATE, and other functions for conversion, or define an uninitialized variable, and directly input the variable.
SQL> create table test (id number, name varchar2 (10), birth date );
Table created.
SQL> insert into test values (1, 'A', SYSDATE );
1 row created.
SQL> insert into test values (null, 'AA', SYSDATE );
1 row created.
SQL> COMMIT;
Commit complete.
SQL> declare
2 v_ SQL varchar2 (4000 );
3 begin
4 v_ SQL: = 'Update test set birth =: vbirth where id is null ';
5 execute immediate v_ SQL using null;
6 commit;
7 end;
8/
Execute immediate v_ SQL using null;
*
ERROR at line 5:
ORA-06550: line 5, column 31:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
SQL> declare
2 v_ SQL varchar2 (4000 );
3 begin
4 v_ SQL: = 'Update test set birth =: vbirth where id is null ';
5 execute immediate v_ SQL using '';
6 commit;
7 end;
8/
PL/SQL procedure successfully completed.
10 introduction to null values in sqlserver
Create a sample table. The Script is as follows:
Create table dbo. cassaba_null
(
Column1 nvarchar (50) not null,
Column2 nvarchar (50) null
)
Go
Insert into dbo. cassaba_null values ('1', null)
Insert into dbo. cassaba_null values ('2', 'string ')
Insert into dbo. cassaba_null values ('3 ','')
Go
A. Use = null/<> null by default, it is indeed not allowed to use = null/<> null to determine whether the null value is so. In fact, SQL Server can use the SET ANSI_NULLS {ON | OFF} setting to control the = null/<> null behavior.
When SET ANSI_NULLS is ON, even if column_name contains a NULL value, the SELECT statement using WHERE column_name = NULL still returns zero rows.
Even if column_name contains non-NULL values, the SELECT statement using WHERE column_name <> NULL still returns zero rows.
However, when SET ANSI_NULLS is OFF, comparison operators equal to (=) and not equal to (<>) do not comply with the ISO standard.
Use the SELECT statement WHERE column_name = NULL to return the rows containing NULL values in column_name.
Use the WHERE column_name <> null select statement to return rows with non-NULL values in the column.
In addition, use the SELECT statement WHERE column_name <> XYZ_value to return all rows that are neither XYZ_value nor NULL.
B. Change the null value connection behavior.
SQL Server provides SET CONCAT_NULL_YIELDS_NULL
{ON | OFF} is used to control the connection between null and other strings.
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating NULL values with strings produces NULL results. For example, SELECT 'abc' + NULL generates NULL.
When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating null values with strings will generate strings themselves (null values are processed as null strings ). For example, SELECT 'abc' + NULL will generate abc.
If SET CONCAT_NULL_YIELDS is not specified, the CONCAT_NULL_YIELDS_NULL database option is applied.
Note: in future versions of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON, and any application that explicitly sets this option to OFF will generate an error.
C. The default value and null value of the Variable
After a variable is named, if no initial value is assigned to it, its value is null. Sometimes you need to pay attention to the difference between the initial null value and the null value assigned to the variable through the select statement. Because this 'null' is not 'null '.
D. null in the subquery
The null values in the subquery are often ignored. First, let's look at the example below.
E. null in Case statement
Do not write the when statement in Case as the when null statement; otherwise, the desired result is not obtained.
The following 1st SQL errors, 2 and 3 are correct.
F. the null-related function ISNULL checks whether the expression is NULL, if yes, replace the NULL value with another value. The COALESCE function returns the first non-NULL value of the specified expression list. NULLIF returns the NULL value when the specified two expressions have the same value, otherwise, the value of the first expression is returned.
Example 1: set ansi_nulls on
Declare @ test1 nvarchar (10)
If (@ test1 = null)
Select 1
Else
Select 2
---------------------------------------------------------------------
Result 2
Example 2: set ansi_nulls off
Declare @ test1 nvarchar (10)
If (@ test1 = null)
Select 1
Else
Select 2
---------------------------------------------------------------------
Result 1 is returned.
Example 3:
Set ansi_nulls on
Select * from dbo. cassaba_null where column2! = Null
---------------------------------------------------------------------
No record returned
Example 4:
Set ansi_nulls off
Select * from dbo. cassaba_null where column2! = Null
---------------------------------------------------------------------
2nd and 3 records are returned.
If you do not want to judge null every time, you can use the isnull function to automatically replace null with a null string for connection.
Example 5:
Declare @ test nvarchar (50)
Select isnull (@ test, '') + 'extend'
Go
Select column1 + isnull (column2, '') as column3 from dbo. cassaba_null
Go
---------------------------------------------------------------------
Example 6:
Declare @ test nvarchar (50)
-- No statement that meets the conditions. Keep the default value null.
Select @ test = column2 from dbo. cassaba_null where column1 = '4'
-- If a qualified statement exists, the returned field value is null and assigned to @ test
Select @ test = column2 from dbo. cassaba_null where column1 = '1'
If the following code uses the value of @ test to determine whether a matching record exists, an error may occur. In this case, we can also use the isnull function to avoid this problem.
Select @ test = isnull (column2, '') from dbo. cassaba_null where column1 = '1'
In the preceding statement, even if a matched record returns null, it will become an empty string assigned to @ test. In this way, the two cases are separated.
Example 7: set ansi_nulls off
Select * from cassaba_null a where a. column2 = (select B. column2 from dbo. cassaba_null B where B. column1 = 1)
---------------------------------------------------------------------
No record is returned no matter whether ansi_nulls is set to on or off. Modify the query statement:
Select * from cassaba_null a where a. column2 in (select B. column2 from dbo. cassaba_null B where B. column1 = 1)
In this way, if ansi_nulls is set to on, no record is returned. If it is set to off, a record is returned.
In this case, if we are sure that the null value record is not required, use the following SQL statement:
Select * from cassaba_null a where a. column2 in (select B. column2 from dbo. cassaba_null B where B. column1 = 1
And B. column2 is not null)
Otherwise, use the following statement:
Select * from cassaba_null a where a. column2 in (select B. column2 from dbo. cassaba_null B where B. column1 = 1
And B. column2 is not null) or a. column2 is null
10 The difference between SQL and oracle null values
A. in SQL Server and oracle, the NULL value is considered to be an infinitely small value. Therefore, if it is sorted in ascending order, it will be ranked first.
B. there are some differences between SQL Server and Oracle in the Process of inserting NULL data values. In SQL Server, we can design the table fields as non-empty, but we can still execute the insert operation using the following statement:
Insert into Table (TestCol) VALUES ('')
The TestCol field has been designed as not null in SQL server. The null field is different from the space field. That is to say, the preceding statement inserts an empty field, but it is not NULL. It is considered to be a violation of non-NULL condition constraints only when this field is not in our insert statement. If NULL is translated as "NULL, it may be easy to confuse. In addition, if our field is of the INT type, if we insert null, we will get a 0 value, that is, SQL server will automatically help us handle the conversion of spaces.
However, in Oracle, this convenience does NOT exist and must be inserted in strict accordance with the rules. That is to say, we can no longer think that the view can meet the design constraints of not null by inserting NULL, the actual content must be inserted to conform to the not null constraint.
Therefore, oracle is more restrictive than SQL Server, which is more rigorous.
