Oracle Database sorts null values and the difference between mull and null strings. oraclemull

Oracle Database sorts null values and the difference between mull and null strings. oraclemull

Processing of null values in order by sorting
When sorting business data, it is found that some field records are null values, and then there is a data size order problem that violates our habits. In Oracle, the default value of null in Order by sorting is the maximum value. Therefore, if it is ASC, it is placed at the end, and DESC is placed at the top of the descending Order. Therefore, we need to process the null record value for the convenience of data analysis.
This is the processing of NULL values in four oracle sorting methods:
1. Use the nvl Function
Syntax: Nvl (expr1, expr2)
If EXPR1 is NULL, EXPR2 is returned. Otherwise, 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 null, as shown in figure
Nvl (person_name, "unknown") indicates that if the value of the person_name field is null, "unknown" is returned. If it is not null, the field value of the person_name is returned.
You can use this function to customize the null sorting position.
2. Use the decode Function
 
The decode function is more powerful than the nvl function. It can also convert an input parameter to a specific value when it is null, such
Decode (person_name, null, "unknown", person_name) indicates that "unknown" is returned when person_name is null. If not empty, the field value of person_name is returned.
This function can also be used to customize the null sorting position.

3. Use nulls first or nulls last syntax, which is the simplest and most commonly used method.

Nulls first and nulls last are syntaxes supported by Oracle Order.

(1) If the expression Nulls first is specified in Order by, it indicates that the records with the null value will rank first (whether asc or desc)

(2) If the Order by expression Nulls last is specified, the null value record will be placed at the end (whether asc or desc)

Examples:
Always put nulls at the beginning:

select * from tbl order by field nulls first

Always put nulls at the end:

select * from tbl order by field desc nulls last

4. Use case syntax
 
The Case syntax is supported after Oracle 9i. It is a flexible syntax and can also be used in sorting.
For example:

Select * from students order by (case person_name when null then 'unknown 'else person_name end)

Indicates that 'unknown 'is returned when the value of the person_name field is null. If the value is not null, 'person_name' is returned.
The case syntax can also be used to customize the null sorting position.
 
Project instance:

  !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 = 'abcdefg'){     $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 .= " ORDER BY 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 FROM (SELECT t.* FROM $joinsqlcollection t {$condition} ) tb_A WHERE ROWNUM <= {$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_key_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;       }  } 

Difference between Null and Null String''
Description:
Q: What is NULL?
A: When we do not know the specific data, it is also unknown. We can use NULL. We call it NULL. in ORACLE, the column length containing NULL values is zero.
ORACLE allows blank fields of any data type, except for the following two cases:
1. primary key field (primary key ),
2. fields with the not null restriction added during definition
Note:
1. It is equivalent to no value and is unknown.
2. NULL and 0, empty strings, and spaces are different.
3. add, subtract, multiply, and divide null values. The result is still null.
4. The NVL function is used for NULL processing.
5. Use the keyword "is null" and "is not null" for comparison ".
6. null values cannot be indexed. Therefore, some data that meets the query conditions may not be found. In count (*), nvl (column name, 0) is used for processing and then query.
7. Sorting is larger than other data (the index is sorted in descending order by default, small → large), so the NULL value is always at the end.
Usage:

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 

Add, subtract, multiply, and divide null values. The result is still null.

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

A record is found.
Note: This record is the null value in the SQL statement.
Set null values for some columns

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

There is a sales table sale with the following structure:

Month char (6) -- month sale number (200001) -- monthly sales amount create table sale (month char (6), sale number); insert into sale values ('123 ', 1000); insert into sale values ('20140901', 200002); insert into sale values ('20160901', 1100); insert into sale values ('20160901', 200003 ); insert into sale values ('20170101', 200005); insert into sale values ('20170101', 1400); insert into sale values ('20170101', 200006 ); insert into sale values ('20170101', 200101); insert into sale values ('20170101', 1100); insert into sale values ('20170101', 200202 ); insert into sale values ('20140901', 200008); insert into sale (month) values ('20160901'); (Note: the limit value of this record is null) commit;

12 records in total

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 

11 records found.
Result description:
The query results indicate that this SQL statement does not query fields with a column value of NULL.
In this case, the field must be 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 

12 records are queried.
Oracle null is used in this way. We 'd better familiarize ourselves with its conventions to prevent incorrect results.

But is there a difference between null and null strings in the char and varchar2 database fields?
Perform 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 ', ''); -- according to the preceding explanation, 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 there is actually no record
Unselected row

SQL> select * from test where b is null; 

---- According to the above explanation, there should be a hop record, but there are actually two records.

A B ---------- ---------- 3 4 SQL>update table test set b='' where a='2'; SQL> select * from test where b=''; 

Unselected row

SQL> select * from test where b is null; A B ---------- ---------- 2 3 4 

Test results show that ''is null for the char and varchar2 fields, but'' after the where condition is not null.
The same applies to default values!

Articles you may be interested in:

• Oracle ORA-22908 (reference for NULL table values) exception analysis and solutions
• Oracle uses order by to sort null values
• How to determine NULL in SQL Server, Oracle, and MySQL
• Detailed differences in NULL processing in MS Server and Oracle