やりたいこと
以下のテーブルがあったとします。
booksテーブル
※price_aとprice_bは、どちらか一方のみに必ず数値が入っている
| title | price_a | price_b |
|---|---|---|
| 本01 | 200 | NULL |
| 本02 | NULL | 100 |
| 本03 | 300 | NULL |
このとき、price_aかprice_bの安い順で並び替えたいという要望がありました。
（上記のテーブルの例だと、「本02, 本01、 本０３」の順になるのがゴール）
COALESCEを使うと解決できる
結論としては、以下のようにCOALESCEを使うことでスッキリ書けます。
sql
SELECT * FROM books ORDER BY COALESCE(price_a, price_b) ASC;
COALEASEは、()のリストを左から順に評価し、NULLではない場合はその値を使い、NULLの場合は次の式を評価してくれます。
https://dev.mysql.com/doc/refman/5.6/ja/comparison-operators.html#function_coalesce
IFNULLも使える
例えばMySQLであれば、IFNULLを使うこともできます。
https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html#function_ifnull
COALEASEは()内にいくつも評価対象を書くことができますが、
IFNULLではIFNULL(a, b)のように、「aがNULLならb」というような書き方ができます。
sql
SELECT * FROM books ORDER BY IFNULL(price_a, price_b) ASC;
うまくいかなかった方法: GREATEST、LEAST
最初はGREATESTYALEASTを使い、price_aとprice_bの最大・最小値をとることでいけないかな？と思ったのですが、実際の値とNULLではLEASTで評価するとNULLを返してしまうようで、期待する結果にはなりませんでした。
sql
SELECT * FROM books ORDER BY LEAST(price_a, price_b) ASC;
+-------+---------+---------+
| title | price_a | price_b |
+-------+---------+---------+
| 本01 | 200 | NULL |
| 本02 | NULL | 100 |
| 本03 | 300 | NULL |
+-------+---------+---------+
SELECT * FROM books ORDER BY GREATEST(price_a, price_b) ASC;
+-------+---------+---------+
| title | price_a | price_b |
+-------+---------+---------+
| 本01 | 200 | NULL |
| 本02 | NULL | 100 |
| 本03 | 300 | NULL |
+-------+---------+---------+
さいごに
最初は以下のように無理やり書いていました。
sql
SELECT * FROM books CASE WHEN price_a IS NULL THEN price_b ELSE price_a END ASC;
もっとすっきり書く方法がきっとあるはず、、、と思って社内で質問したところ上記の知見が得られたので良かったです。