The sorting of NULL values in Oracle database and the difference between Mull and empty strings _oracle

Method of NULL value processing for ORDER by ordering
when the business data is sorted, it is found that some fields have a null value, then the order of the data is inconsistent with our usage. In Oracle, it is assumed that NULL is the maximum value by default when ordered by, so if ASC ascending is the last, and Desc descending is the top. So, why do we analyze the intuitive convenience of data, we need to deal with the value of NULL records.
This is the method for handling null values in four Oracle sorts:
1. Use NVL function
syntax: NVL (EXPR1, EXPR2)
If EXPR1 is null, return EXPR2, or return to EXPR1.

  SELECT Name,nvl (To_char (COMM), ' not application ') from TABLE1;

The NVL function can be converted to a specific value when the input parameter is empty, as
NVL (Person_name, "Unknown") indicates that if the Person_name field value is empty, it returns "Unknown" and returns the Person_name field value if not null.
This function allows you to customize the sort location of NULL.
2. Use decode function

The Decode function is more powerful than the NVL function, and it can also be converted to a specific value when the input parameter is empty, as
Decode (Person_name,null, "Unknown", person_name) means returning "unknown" when Person_name is empty, and returning the field value of Person_name if not null.
This function also allows you to customize the sort location of NULL.

3. Use the nulls one or nulls last syntax, the simplest and most commonly used method.

Nulls-I and Nulls last are Oracle ORDER by supported syntax

(1) If the expression nulls first is specified in order by, the record that represents the null value is ranked at the front (whether ASC or DESC)

(2) If an expression nulls is specified in order by, the record that represents the null value is last (whether ASC or DESC)

Examples of how to use it are:
Always put the nulls at the front:

SELECT * from TBL Order by field nulls

Always put the nulls at the end:

SELECT * from tbl Order BY field Desc Nulls last

4. Use case syntax

The case syntax, which is supported by Oracle 9i, is a more flexible syntax and can also be applied in sorting
Such as:

SELECT * FROM
 students the
 person_name when
      null then
       ' unknown '
      else
       person_name End
     )

Returns ' Unknown ' when the Person_name field value is empty, and returns person_name if not null
The case syntax also allows you to customize the sort location of NULL.

Project Example:

  !defined (' path_admin ') && exit (' forbidden '); Class Mod_gcdownload {public static function get_gcdownload_datalist ($start = 0, $rowsperpage = page_rows, $datestart = ', $dateend = ', $ver = ', $coopid = ', $subcoopid = ', $sortfield = ', $sorttype = ', $pid = 123456789, $plat = ' ABCD 
    EFG ') {$sql = '; $condition = Empty ($datestart)? 
    "Where 1=1": "Where T.statistics_date >= ' $datestart ' and t.statistics_date <= ' $dateend '"; 
    if ($ver) {$condition. = "and t.edition= ' $ver '"; 
    } if ($coopid) {$condition. = "and t.suco_coopid= $coopid"; 
    } if ($subcoopid) {$condition. = "and t.suco_subcoopid= $subcoopid"; 
    } if ($sortfield && $sorttype) {$condition. = "t.{$sortfield} {$sorttype} NULLS last"; 
    }elseif ($sortfield) {$condition. = "ORDER by t.{$sortfield} desc NULLS last"; 
   }else{$condition. = "ORDER BY t.statistics_date Desc NULLS last"; } $finish = $start + $rowsperpage; $joinsqlcollection = "(select Tc.coop_name, Tsc.suco_name, Tsc.suco_coopid,tsc.suco_subcoopid, S.edition, S.new_user, D.one_user, D.three_user, D.seven_user, s.statistics_date from ((pdt_stat_newuser_{$pid}_{$plat} s left JOIN Pdt_days_ dl_remain_{$pid}_{$plat} D on S.statistics_date=d.new_date and S.subcoopid=d.subcoopid and S.edition=d.edition) left Join Tbl_subcooperator@jtuser1.net@jtinfo TSC on S.subcoopid=tsc.suco_subcoopid) left JOIN Tbl_cooperator@JTUSER1.NET 
    @JTINFO TC on tsc.suco_coopid=tc.coop_id)) "; $sql = "SELECT * from" (select Tb_a.*, rownum as RN to (select t.* from $joinsqlcollection t {$condition}) tb_a WHERE RO 
    Wnum <= {$finish}) tb_b WHERE tb_b.rn>{$start} "; $countsql = "Select COUNT (*) as Totalrows, SUM (T.new_user) as Totalnewusr,sum (T.one_user) as Totaloneusr,sum (t.three_ 
    User) as Totalthreeusr,sum (T.seven_user) as totalsevenusr from $joinsqlcollection t {$condition} "; 
    $db = Oralceinit (1); $Stidquery = $db->query ($sql, false); 
    $output = Array (); while ($row = $db->fetcharray ($stidquery, $skip = 0, $maxrows =-1)) {$output [' data '] [] = Array_change_ke 
    Y_case ($row, case_lower); 
    $count _stidquery = $db->query ($countsql, false); 
    $row = $db->fetcharray ($count _stidquery, $skip = 0, $maxrows =-1); 
    $output [' Total ']= array_change_key_case ($row, case_lower); echo "<br/>". ($sql). " 
    <br/> ";  
   
  return $output; 

 } 
 
}

The difference between null and empty string '
meaning Explanation:
Q: What is NULL?
A: When we do not know what the specific data is, it is unknown, we can use NULL, we call it null, in Oracle, the table column length with null value is zero.
Oracle allows NULL fields for any one data type, except for the following two scenarios:
1, the primary key field (primary key),
2. Fields that have been added not NULL restrictions when defined
Description
1, equivalent to no value, is unknown.
2, null and 0, empty strings, spaces are different.
3, the null value to do add, subtract, multiply, and other operations, the result is still empty.
4, NULL processing using the NVL function.
5. Use keywords "is null" and "is not NULL" when comparing.
6, null value can not be indexed, so the query when some of the conditions of the data may not be found out, COUNT (*), with NVL (column name, 0) after processing.
7, the sort is larger than other data (index default is descending order, small → large), so the null value is always in the end.
How to use:

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 

The null value is added, subtract, multiply, divide and so on operation, the result is still empty.

Sql> select 1+null from dual; 
Sql> select 1-null from dual; 
Sql> select 1*null from dual; 
Sql> select 1/null from dual; 

Query to a record.
Note: This record is the null in the SQL statement
Set some column null values

UPDATE table1 set column 1=null where column 1 is not NULL; 

There is an existing sales table sale, the table structure is:

Month char (6)--month 
sell number (10,2)--monthly Sales Amount 
CREATE TABLE sale (month char (6), sell number); 
INSERT into sale values (' 200001 ', 1000); 
INSERT into sale values (' 200002 ', 1100); 
INSERT into sale values (' 200003 ', 1200); 
INSERT into sale values (' 200004 ', 1300); 
INSERT into sale values (' 200005 ', 1400); 
INSERT into sale values (' 200006 ', 1500); 
INSERT into sale values (' 200007 ', 1600); 
INSERT into sale values (' 200101 ', 1100); 
INSERT into sale values (' 200202 ', 1200); 
INSERT into sale values (' 200301 ', 1300); 
INSERT into sale values (' 200008 ', 1000); 
INSERT INTO sale (month) VALUES (' 200009 '); (note: The sell value of this record is empty) 
commit; 

Total Input 12 Records

Sql> select * FROM sale where sell like '% '; 
MONTH SELL 
--------------- 
200001 1000 
200002 1100 200003 1200 200004 
1300 
200005 1400 
200006 1500 
200007 1600 
200101 1100 200202 1200 200301 
1300 
200008 1000 

Query to 11 records.
Results show:
Query results indicate that this SQL statement queries a field that does not have the column value null
This should be handled in addition to the case where the field is null.

Sql> select * FROM sale where sell like '% ' or sell is null; 
Sql> select * FROM sale where NVL (sell,0) like '% '; 
MONTH SELL 
--------------- 
200001 1000 
200002 1100 
200003 1200 200004 1300 200005 1400 200006 1500 
200007 1600 
200101 1100 200202 1200 200301 1300 200008 1000 
200009 

Query to 12 records.
Oracle's null value is such a usage, we'd better familiarize ourselves with its conventions in case the results are not correct.

But is there a difference between null and empty strings in database fields for char and VARCHAR2 types?
Make a test:

CREATE TABLE Test (a char (5), B char (5)); 
sql> INSERT INTO Test (A,B) VALUES (' 1 ', ' 1 '); 
sql> INSERT INTO Test (A,B) VALUES (' 2 ', ' 2 '); 
sql> INSERT INTO Test (A,B) VALUES (' 3 ', ')--as explained above, the B field has a value of 
sql> insert INTO Test (a) VALUES (' 4 '); 
Sql> select * from test; 
A B 
-------------------- 
1 1 
2 2 
3 
4 

Sql> SELECT * FROM test where b= ';

----According to the above explanation, there should be a record, but actually no record
No rows selected

----As explained above, there should be a jump record, but in fact there are two records.

A B 
-------------------- 
3 
4 
sql>update table test set b= ' where a= ' 2 '; 
Sql> SELECT * FROM test where b= '; 

No rows selected

Sql> SELECT * FROM test where b is null; 
A B 
-------------------- 
2 
3 
4 

The test results show that "is null for char and VARCHAR2 fields, but not null for where condition."
The same is true for default values!