ORDER BYで指定したカラムにNULLが含まれる場合、NULLの現れる順序はRDBMS毎に異なるので注意が必要です。
変更履歴
- 2017/07/03 12:30 DB2 11.1の変更点について修正
- 2017/02/28 16:00 DB2の実装について修正
RDBMSごとの実装
ORDER BY時にNULLを最小の値として扱うRDBMSと、最大の値として扱うRDBMSの2種類が存在します。
RDBMSによっては、ORDER BYのオプションとしてNULLS FIRST/LASTを指定することで、NULLの順序を指定することが出来ます。
SELECT colname FROM tablename ORDER BY colname NULLS FIRST
SELECT colname FROM tablename ORDER BY colname NULLS LAST
以下のRDBMSについて、NULLのデフォルト順序（最小値/最大値のどちらとして扱うか）とNULLS FIRST/LASTのサポート状況を調べてみました。
- DB2 for LUW 11.1
- Oracle 12c
- SQL Server 2016
- PostgreSQL 9.1
- MySQL 5.7
結果は以下のように見事にバラバラとなっていました。
| RDBMS | NULLの扱い | NULLS FIRST/LAST |
|---|---|---|
| DB2 10.5 | 最大値 | × |
| DB2 11.1 | 最大値 | ○ |
| Oracle | 最大値 | 〇 |
| SQL Server | 最小値 | × |
| PostgreSQL | 最大値 | 〇 |
| MySQL | 最小値 | × |
10.5までのDB2ではマニュアルを見ると一見NULLS FIRST/LASTが使えるように見えますが、実際はデフォルトの動作を変えない以下の指定の仕方しか許可されていません。
ORDER BY colname ASC NULLS LAST
ORDER BY colname DESC NULLS FIRST
なお、PostgreSQLのみの機能としてCREATE INDEX時のオプションでNULLS FIRST/LASTの指定が可能となっています。これにより索引のB-tree上でのNULLの配置を制御できるため、ORDER BY句で指定するNULLS FIRST/LASTと同じ順序を指定しておくと、索引を使用してソート処理をスキップする実行計画が選択可能になります。
SQL標準での規定
SQL標準ではNULLのデフォルト順序は実装依存であるとしています。おそらく、規格の策定当初から実装ごとに仕様がバラバラで統一できなかったのでしょう。
NULLS FIRST/LASTはSQL:2003で標準化されたようです。これにより実装差異をある程度吸収可能になりましたが、先述のように未サポートのRDBMSもあります。
PostgreSQLのCREATE INDEXのNULLS FIRST/LASTについてですが、そもそもSQL標準は物理実装を規定しないためCREATE INDEX自体がSQL標準にはありません。
参考資料
- SQL-99 Complete, Really
- IBM Information Center DB2 for Linux UNIX and Windows 11.1.0
- Oracle Database Online Documentation 12c Release 1 (12.1)
- SQL Server Transact-SQL Reference (Database Engine)
- PostgreSQL 9.1.24 Documentation
- MySQL 5.7 Reference Manual