Understand the principle of null in depth and the principle of null in Depth

Understand the principle of null in depth and the principle of null in Depth

-- Null principle -- oracle always treats null and null strings ''with the same length as 0. For example, ''is null is true,'' = null is false, if a varchar2: = ''is declared, a is null is true, and a ='' is false> -- 1. null operations-arithmetic expressions and null operations are always null. In fact, all operators except | join operators, if one operator is null, the result is null. -------------------------------- Null Operator operation ------------------------------ arithmetic operation. If one operand is null, the result is nullselect null + 10, null * 10, null-10, null/10 from dual; -- join operator | except for null, which is equivalent to an empty string select null | 'abc', ''| 'abc' from dual; -- abc, abc -- 2. null in the function use create table nulltest asselect null a from dualunion allselect 1 from dualunion allselect 2 from dual; alter table test2 add constraints uk_nulltest_id primary key (c Ity); -- null is used in group functions. sum, avg, min, and max all ignore the null value select sum (a) from nulltest; -- 3 select avg (a) from nulltest; -- 1.5 select min (a) from nulltest; -- 1 select max (a) from nulltest; -- 2 -- null in the count function -- (1) If you specify count (*) or count (1) does not ignore null (count (*) and count (1), and there is no difference in efficiency) -- (2) if it is count (column name) the nullselect count (*) from nulltest; -- 3 contains null computing select count (1) from nulltest; -- 3 contains null computing select count (a) from nulltes T; -- 2 ignore nullselect count (rowid) from nulltest; -- 3--3. null is used in the condition. If an operand contains null, true or false can be returned only after comparison between is null and is not null. Otherwise, the returned result can only be unkown. -- 4. the relationship between null and indexes and execution plans (null is used in query optimization) -- if it is a B * tree Index, a single column index, and the index column has a null value, is null does not go through the index, of course, is not null may take the index and look at the cbo plan. -- if you want to take the index, you can use bitmap index or composite index to ensure that the other column does not have null.