背景

値に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の順に記述していた。
下記の処理順となるので、処理順を考慮する必要がありました。

2の場合

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は最小とみなされて先に出力される。

参考