背景
値にNULLを含むレコードを並び替える時、結果の最後にNULLを出力したいケースがありました。
ORDER BY xxx IS NULL ASCを使えばいい、と思っていたらうまくいかずハマってしまったので、反省として記します。
例
下記のテーブルを例に考えてみます。
- 複数の飲食店でスマホから注文、決済ができるシステムを導入している。
- 利用者が注文、決済を実行すると、注文情報としてオーダーテーブル(Orders)に注文先の店舗ID(store_id)と決済種別(payment_type)が登録される。
- スマホでの注文時、非対面の決済(クレジットカード決済、QRコード決済)の場合は対応する数値が登録される。現金払いの場合はNULLが登録される。
| id | store_id | payment_type |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 1 | null |
| 3 | 1 | 3 |
| 4 | 1 | 2 |
| 5 | 1 | 1 |
| 6 | 2 | null |
| 7 | 2 | 2 |
| 8 | 2 | 1 |
要求事項
- 複数の飲食店から、決済種別を含めた注文情報を出力したいという要望があった。
- 決済種別の並び順は固定したい。
要求事項を満たす出力結果
- 下記のように、
store_idの昇順、payment_typeの昇順としたい。 - 現地決済を意味する
NULLが存在する場合、各店舗の最後に出力したい。
| store_id | payment_type |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | null |
| 2 | 1 |
| 2 | 2 |
| 2 | null |
| 3 | 1 |
ソートでNULLを最後に並べたい
store_idの昇順、payment_typeの昇順としたいので、それぞれをORDER BY句に指定します。
SELECT
store_id, payment_type
FROM
Orders
ORDER BY
store_id ASC,
payment_type ASC;
しかし、payment_type ASCと指定した場合、NULLは最小値とみなされ各店舗の最初に並びます。
下記の要求事項を満たしません。
現地決済を意味する
NULLが存在する場合、各店舗の最後に出力したい。
| store_id | payment_type |
|---|---|
| 1 | null |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | null |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
NULLとNULLでない値を分けたいので、IS NULLを使用します。
IS NULLはboolean型の0(false)または1(true)を返すので、ORDER BY句に指定すると0(false)→1(true)の順に並びます。つまりNULL以外の値が先に、NULLが後になります。
-
NULLでない場合 →0(false) -
NULLの場合 →1(true)
SELECT
store_id, payment_type
FROM
Orders
ORDER BY
store_id ASC,
payment_type IS NULL ASC,
payment_type ASC;
想定通りの出力結果になりました！
| store_id | payment_type |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | null |
| 2 | 1 |
| 2 | 2 |
| 2 | null |
| 3 | 1 |
ハマったところ
-
payment_type ASCを記述していなかった。
ORDER BY xxx IS NULL ASCがNULLとNULLでない値を分けるだけ、であることを理解していませんでした。
NULLとNULLでない値を分けたあと、NULLでない値で昇順にする必要がありました。 -
payment_type IS NULL ASC→payment_type ASCの順に記述していた。
下記の処理順となるので、処理順を考慮する必要がありました。
SELECT
store_id, payment_type
FROM
Orders
ORDER BY
store_id ASC,
payment_type ASC, -- 後続の条件よりも優先され、NULLは最小とみなされて先に出力される
payment_type IS NULL ASC;
a. payment_type ASCの指定でNULLが先に来る。
b. payment_type IS NULL ASCは0(false)→1(true)に並ぶのでNULLが後に来るはず。
c. しかし、SQLの処理順的にpayment_type ASCが優先されNULLは最小とみなされて先に出力される。