Shulou(Shulou.com)06/01 Report--

We all know that order by is used to sort the result set of the query in the Oracle SQL statement, and the null value is a very special value in Oracle. If the column specified by order by has a null value, what is the sorting result?

Let's do a set of experiments to observe how Oracle deals with NULL in order by.

Version 11.2.0.4

1. Create a test table and insert test data

Zx@ORCL > create table t (id number,name varchar2 (10)); Table created.zx@ORCL > insert into t values (1); row created.zx@ORCL > insert into t values (2); row created.zx@ORCL > insert into t values (3); row created.zx@ORCL > insert into t values (4); row created.zx@ORCL > insert into t values (5); row created.zx@ORCL > insert into t values (6 null); 1 row created.zx@ORCL > commit Commit complete.zx@ORCL > select * from t; ID NAME--1 zx 2 wl 3 zxt 4 5 yhz 66 rows selected.

2. Test order by

Zx@ORCL > select * from t order by name asc; ID NAME- 2 wl 5 yhz 1 zx 3 zxt 6 46 rows selected.zx@ORCL > select * from t order by name desc ID NAME- 4 63 zxt 1 zx 5 yhz 2 wl6 rows selected.

When you see different sorting methods, the null values sort in different positions. Ascending order (asc) NULL ranked last, descending order (desc) NULL ranked first.

Let's take a look at how the official document describes it.

ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

It can be seen that our experimental results are consistent with those described in the official document. You can also use NULLS FIRST | NULLS LAST to determine whether the value of NULL is at the top or last.

3. Do the experimental verification again

Zx@ORCL > select * from t order by name asc nulls first; ID NAME- 6 4 2 wl 5 yhz 1 zx 3 zxt6 rows selected.zx@ORCL > select * from t order by name asc nulls last ID NAME- 2 wl 5 yhz 1 zx 3 zxt 6 46 rows selected.zx@ORCL > select * from t order by name desc nulls first ID NAME--4 6 3 zxt 1 zx 5 yhz 2 wl6 rows selected.zx@ORCL > select * from t order by name desc nulls last ID NAME- 3 zxt 1 zx 5 yhz 2 wl 6 46 rows selected.

You can see from the result that using NULLS FIRST | NULLS LAST can directly control whether the null value is at the beginning or the tail of the sort result.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.