NULL in SQL

The null summary in Oracle SQL is as follows:

1.1 Null

? The result of a null value calculation is still null

? Null is an unassigned, unknown, or non-applicable value

? Null is not 0, nor is it a space

1.1.1 Operation of NULL values

The result of the arithmetic expression for the null argument is still NULL

Sql> Select ' * ' | | 1/null| | ' * ' Res1, ' * ' | | 1*null| | ' * ' Res2, ' * ' | | (1-null) | | ' * ' Res3 from dual;

Re-re 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

Take the mold

Sql>select ' * ' | | MoD (9,null) | | ' * ' Res fromdual;

RE

--

**

From null/0 without error, we can extrapolate that when Oracle finds NULL in an arithmetic expression, it does not really evaluate the expression, but instead returns null directly.

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

Either Null!=null or Null=null evaluates to False (in effect, NULL is returned, NULL behaves as false in logic), and is visible whether it is arithmetic or logical, as long as NULL participates, the result is null

1.1.2 Null value to String connection

NULL when connected to a character field, behaves as if nothing "", equivalent to 0 characters

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

‘*

--

**

Note: This involves the display of Chinese, if garbled, please confirm database service nls_database_parameters, server echo $NLS _lang/set Nls_lang, and the client V$nls_ Parameters The encoding settings are consistent.

1.1.3 Null Query mode

Sql> SELECT * from emp where comm is null;

Other query methods filter the null value of the column first, that is, NULL does not participate in a query other than is null/is 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 The order of the column where null is located

In order by ordering, 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

Rowsselected.

1.1.5 is null does not go index

In SQL optimization, there are times when SQL statements are indexed and should be indexed, but in the end there is no index, and the is null here is one of those cases.

CREATE TABLE n1 (SID Integer,sname Varchar2 (120));

Insert 1W Record, sname value of 1th w is null

Begin

for i in 1.. 9999 Loop

Insert into N1 values(i,' name '| | I);

if MoD (i,+) = 0 Then

Commit ;

End if ;

End Loop ;

Insert into N1(sid)values(10000);

Commit ;

End ;

Querying sname column values is an index range scan.

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 |    |     2   (0) | 00:00:01| | *  1 |  INDEX RANGE scan| N1_sname_ind |     1 |    |     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 in the 1W row in the null record in Sname, there is still no index, i.e. is null does not walk 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 |    |     9   (0) | 00:00:01 | | *  1 |  TABLE ACCESS full| N1 |     1 |    |     9   (0) | 00:00:01 |--------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------   1-filter ("SNAME" is NULL) Note-----   -The dynamic sampling used for this statement (level=2) rows selected.

If the actual situation does require an IS NULL query to go index it? This can be done by creating a federated index.

Drop index N1_sname;

CREATE INDEX N1_sname_ind on N1 (SNAME,SID);

Sql> explain plan for SELECT * from N1 where the SID is isn't 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 |    |     3   (0) | 00:00:01| | *  1 |  INDEX RANGE scan| N1_sname_ind |     1 |    |     3   (0) | 00:00:01|-------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------   1-access ("SNAME" is NULL)       filter ("SID" was not NULL) Note-----   -Dynamic sampling used for this statement (level=2) rows selected .

You can see that the resources consumed by SQL queries are significantly reduced after you create a federated index.

It is important to note that our queries are most frequently used by the columns, such as sname to be placed in the first column of the Federated Index, and to go along with the federated Index, which requires all the fields that the federated index contains in the condition following the where, which is why the SID is added to this field.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

NULL in SQL