Null and SQLnull in SQL

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.