Null and SQLnull in SQL

Null and SQLnull in SQL

A summary of null in Oracle SQL is as follows:

1.1 null

The null value is still null.

Null is an unallocated, unknown, or not applicable value.

Ø null is neither 0 nor a space.

 

1.1.1 null Value Calculation

The result of the arithmetic expression of the null parameter is still null.

SQL> select '*' | 1/null | '*' res1, '*' | 1 * null | '*' res2, '*' | (1-null) | '*' res3 from dual;

RE

------

******

SQL> select null/0 from dual;

NULL/0

----------

SQL> select 1/0 from dual;

Select1/0 from dual

*

ERROR atline 1:

ORA-01476: divisor is equal to zero

Modulo

SQL> select '*' | mod (9, null) | '*' res fromdual;

RE

--

**

From the case where no error is reported for NULL/0, we can infer that Oracle finds that when there is null in the arithmetic expression, it does not actually calculate the expression, but directly returns null.

SQL> select 1 from dual where null! = Null;

No rowsselected

SQL> select 1 from dual where null = null;

No rowsselected

SQL> select 1 from dual;

1

----------

1

Whether it is null! The results of = null or null = null are all false (in fact, null is returned, and null is expressed as false in the logic). It can be seen that, whether it is an arithmetic operation or a logical operation, as long as null is involved, the result is null.

1.1.2 null value and string connection

When null is connected to a balanced field, why is there no ""? It is equivalent to zero characters.

SQL> select '*' | null | '*' from dual;

'*

--

**

NOTE: If Chinese characters are garbled during the experiment, check whether the Database Service nls_database_parameters, server echo $ NLS_LANG/set NLS_LANG, and client v $ nls_parameters encoding settings are consistent.

1.1.3 null Query Method

SQL> select * from emp where comm is null;

Other query methods first filter the records of the rows where the column's null value is located, that is, null does not participate in queries other than is null/is not null.

 

SQL> select count (*) from emp;

COUNT (*)

----------

14

SQL> select count (*) from emp where comm <= 500;

COUNT (*)

----------

3

SQL> select count (*) from emp where comm> 500;

COUNT (*)

----------

1

 

1.1.4 sorting of null Columns

In order by sorting, the null value is the largest.

SQL> select ename, comm from emp order by comm;

 

TURNER 0

ALLEN 300

WARD 500

MARTIN 1400

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

BLAKE

JONES

SMITH

 

CLARK

14 rowsselected.

1.1.5 is null without Indexing

In the optimization of SQL, sometimes the index should be taken if the SQL statement has an index, but the index is not taken in the end. Here, is null is one of the cases.

Create table n1 (sid integer, sname varchar2 (120 ));

Insert 1 million records. The sname value of the 1W record is null.

Begin

For I in 1 .. 9999 loop

Insert into n1 values (I, 'name' | I );

If mod (I, 100) = 0 then

Commit;

End if;

End loop;

Insert into n1 (sid) values (10000 );

Commit;

End;

The sname column value is queried by index range scanning.

SQL> explain plan for select * from n1 where sname = 'name1';Explained.SQL> select * from table(dbms_xplan.display);Plan hash value: 3644017351--------------------------------------------------------------------------------| Id  | Operation | Name | Rows| Bytes | Cost (%CPU)| Time|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT ||     1 |    75 |     2   (0)| 00:00:01||*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     2   (0)| 00:00:01|--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("SNAME"='name1')Note-----   - dynamic sampling used for this statement (level=2)

Is null query. Although there is only one row of null records in the sname, but it still does not take the index, that is, is null does not take the index.

SQL> explain plan for select * from n1 where sname is null;Explained.SQL> select * from table(dbms_xplan.display);Plan hash value: 2416923229--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    75 |     9   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| N1 |     1 |    75 |     9   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("SNAME" IS NULL)Note-----   - dynamic sampling used for this statement (level=2)17 rows selected.

What if the actual condition does require the is null query to take the index? You can create a joint index.

Drop index n1_sname;

Create index n1_sname_ind on n1 (sname, sid );

 

SQL> explain plan for select * from n1 where sid is not null and sname is null;

SQL> select * from table(dbms_xplan.display);Plan hash value: 3644017351-------------------------------------------------------------------------------| Id  | Operation | Name | Rows| Bytes | Cost (%CPU)| Time|-------------------------------------------------------------------------------|   0 | SELECT STATEMENT ||     1 |    75 |     3   (0)| 00:00:01||*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     3   (0)| 00:00:01|-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("SNAME" IS NULL)       filter("SID" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)18 rows selected.

You can see that after the Union index is created, the resources consumed by SQL queries are significantly reduced.

It should be noted that we query the most frequently used columns, for example, the sname should be placed in the first column of the Union index, and the Union index should be used at the same time, all fields contained in the joint index must appear in the condition after the where clause. This is why the field sid is added.

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.