NULL Order Behavior

1 Introduction

An ORDER BY clause allows you to specify the order in which rows appear in the result set. For instance, sorting on a column in a data grid sorts the data of the column in either ascending (smallest value first) or descending (largest value first) order. The default order is ascending.

However, NULL is a special marker used in SQL to indicate that a data value does not exist in the database.

If you have NULL values in the column and a sort is applied on the column, the decision whether the NULLs should come first or last varies per database type.

2 Default NULL Value Sort Order Behavior by Database Type

2.1 HSQLDB

If you specify the ORDER BY clause, a NULL value always comes first before any non-NULL value, irrespective of the sort order.

2.2 MARIADB, MYSQL, SAP HANA, & SQLSERVER

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value. Using the DESC order, the NULL comes last.

2.3 DB2, ORACLE, & POSTGRESQL

If you specify the ORDER BY clause, NULL values by default are ordered as more than values that are not NULL. Using the ASC order, a NULL value comes after any non-NULL value. Using the DESC order, the NULL comes first.

3 Overview of Default NULLs Sort Order

This table presents the NULLs default sort ordering provided by different database types: