欅樹雑記: 特定のレコードだけ常に先頭or末尾に固定して残りをソートするSQL

2022年8月7日日曜日

たとえばこんなテーブル構成＆レコードを考えます。

id, name, age

このレコード、ageがnullのレコード（年齢情報が未入力の場合とでも考えてください）は常に先頭にして取得したい場合、どのようなSQLを書けばいいでしょうか。

※このテーブルはただの例です。「年齢は毎年変わるからDBのレコードに含めるのは適切じゃないぞ」というツッコミはよそでやってください。

何も考えずに書くとどうなる？

SELECT * FROM users ORDER BY age;

この挙動はRDBMS依存です。たとえばMySQLやSQL Serverでは希望通りnullレコードが先頭に来ますが、PostgreSQLやOracleでは最後に来るようです（実際に試したわけではなく検索して出てきた情報ですが）。

以下の構文が標準SQL（SQL:2003）で登場しました。

SELECT * FROM users ORDER BY age NULLS FIRST;

NULLS FIRSTをNULLS LASTにすると、nullレコードが最後に来ます。ただしこの書き方は、MySQLなどの一部のRDBMSでは現時点でもサポートされていません。

NULLS FIRSTよりも移植性の高い書き方は以下の通り。

SELECT * FROM users ORDER BY (age IS NULL) DESC, age;

ageでソートする前にage IS NULLの降順でソートすることで、ageがnullのレコードだけが先頭に集まります。nullのレコードを最後に集めたい場合は(age IS NULL ASC)でOK。基本的な構文しか使っていないので、MySQLでも動きます。

この書き方を自力で思いついたときは「おー、やったぁ！（｀・ω・´）」という感じでかなり興奮したんですが、後で調べてみたら割と有名なイディオムらしくて（ ´・ω・｀）となりました。

この書き方は意外と応用が広く、nullを固定する以外にも色々な使い方ができます。

例えば、「ageでソートするけどidが1のレコードを先頭に固定したい」場合。idがのユーザーは特殊なユーザーなので、年齢でソートする場合もこのユーザーだけは先頭にしたい・・・という状況ないですか？ないですね。

SELECT * FROM users ORDER BY (id = 1) DESC, age ASC;

先程の構文が理解できれば説明不要ですね。(id = 1 DESC)でidが1のレコードだけ先頭に集まります。

ECサイトなどで、型番を"A-1"のように「カテゴリー - 連番」で表すことは多いんじゃないかと思います。

「カテゴリーが"XX"の商品はおすすめ品だから、どの基準で並べ替えても先頭に固定したい」という状況ないですか？ないですね。

型番のカテゴリーをmodel_type、連番をmodel_numberで表している場合（この2つで複合一意制約）を仮定すると、

SELECT * FROM products ORDER BY (model_category = "XX") DESC, model_category ASC, model_number ASC;

これもあらためて説明の必要はないかと思います。

記憶の片隅に残しておくと、いざというときに役に立つかもしれません。