【MySQL】【PostgreSQL】ORDER BY時に null を上にするか下にするかを制御する│株式会社シーポイントラボ

　SQL を動かせる RDBMS(関係データベース管理システム)は MySQL, PostgreSQL, OracleDB, MariaDB, SQLite などいくつかあります。そしてその中では差異があります。この差異は実装されている機能であったり、計算ロジックであったり、構文の細部であったり様々です。例えば、MySQL と PostgreSQL では ORDER BY した際の NULL の順番が異なるという差異があります。ここではこの際についてこれは次の様になります。

-- DB定義
CREATE TABLE tests
(
    id    INTEGER,
    title VARCHAR(100)
);
INSERT INTO tests (id, title) VALUES (1, 'Hello');
INSERT INTO tests (id, title) VALUES (2, 'World');
INSERT INTO tests (id, title) VALUES (3, NULL);
INSERT INTO tests (id, title) VALUES (4, '');
-- title 昇順で取得
SELECT * FROM tests ORDER BY title;
-- MySQL 8.0.28
3,
4,""
1,Hello
2,World
 
-- PostgreSQL 14.2
4, ""
1, Hello
2, World
3, 

　見ての通り MySQL では NULL が最上位になるのに対して PostgreSQL では最下位になっています。これを制御したくなる時があります。

　解決方法は少なくとも三つあります。一つ目はそもそも NULL を認めない方法、二つ目は生成列を用いる方法、三つ目は ORDER BY 命令を工夫する方法です。

　一つ目のそもそも NULL を認めない方法は文字通りです。NULL が入らなければ悩む必要もないとそういう話です。もし NULL が不要なカラムであれば NOT NULL 制約をつけておくだけでこの並び替え問題に直面しなくなります。一番シンプルな手法であり可能ならばこの方法が無難です。
　この方法は次の様にできます。

-- DB定義
CREATE TABLE tests
(
    id    INTEGER,
    title VARCHAR(100) NOT NULL DEFAULT '' -- NOT NULL 制約を追加
);
INSERT INTO tests (id, title) VALUES (1, 'Hello');
INSERT INTO tests (id, title) VALUES (2, 'World');
INSERT INTO tests (id) VALUES (3);
INSERT INTO tests (id, title) VALUES (4, '');
-- title 昇順で取得
SELECT * FROM tests ORDER BY title;
-- MySQL 8.0.28
3,""
4,""
1,Hello
2,World
 
-- PostgreSQL 14.2
3, ""
4, ""
1, Hello
2, World

　二つ目の生成列を用いる方法は NULL を持つカラムと同期する NULL を持たないカラムを作る方法です。生成列は同行の他の値を元に生成される値を持つ列です。これを用いて NULL 値を含む並び替えたい列に対応する並び替え用のカラムを定義します。概ね多くの DB で使えインデックスをつけることも可能な方法ですが、構文の差異が多かったり SQLite で使える様になったのが最近などといくらかつまづきやすいです。

MySQL ：： MySQL 8.0 リファレンスマニュアル：： 13.1.20.8 CREATE TABLE および生成されるカラム
 PostgreSQL： Documentation： 14： 5.3. Generated Columns
PostgreSQL 13.1文書 5.3. 生成列

-- DB定義
CREATE TABLE tests
(
    id    INTEGER,
    title VARCHAR(100),
-- MySQLの場合は↓
    title_order VARCHAR(100) AS (IF(title IS NULL, '', title)) STORED
-- PostgreSQLの場合は↓
    title_order VARCHAR(100) GENERATED ALWAYS AS (CASE WHEN title IS NULL THEN '' ELSE title END) STORED
);
-- STORED とつけられた生成列はインデックスをつけることができます。
-- STORED とつけられた生成列は実際にその値をディスクに格納しており容量を占めます
CREATE INDEX tests_title_order_index ON tests (title_order);
INSERT INTO tests (id, title) VALUES (1, 'Hello');
INSERT INTO tests (id, title) VALUES (2, 'World');
INSERT INTO tests (id, title) VALUES (3, NULL);
INSERT INTO tests (id, title) VALUES (4, '');
-- title_order （title の NULL を変換した列）昇順で取得
SELECT * FROM tests ORDER BY title_order;
-- MySQL 8.0.28
id,title,title_order
3,,""
4,"",""
1,Hello,Hello
2,World,World
 
-- PostgreSQL 14.2
id,title,title_order
3,,""
4,"",""
1,Hello,Hello
2,World,World

　三つ目の ORDER BY 命令を工夫する方法です。これは ORDER BY の対象を単なるカラムでなく式にすることで実現できます。既にある定義に触らずに使える手軽さが魅力です。一方でインデックスを完全に使えない（MySQL では該当カラムにインスタンスをつけても explain した際に Using index; Using filesort とでます）という欠点もあります。このため対象のデータが多い検索ではパフォーマンス上の問題が現れやすいです。
　これは次の様にできます。

-- DB定義
CREATE TABLE tests
(
    id    INTEGER,
    title VARCHAR(100)
);
INSERT INTO tests (id, title) VALUES (1, 'Hello');
INSERT INTO tests (id, title) VALUES (2, 'World');
INSERT INTO tests (id, title) VALUES (3, NULL);
INSERT INTO tests (id, title) VALUES (4, '');
-- title 昇順で取得
-- MySQL の場合は CASE の代わりに IF でも可
SELECT * FROM tests ORDER BY CASE WHEN title IS NULL THEN '' ELSE title END;
-- MySQL 8.0.28
3,NULL
4,""
1,Hello
2,World
 
-- PostgreSQL 14.2
3, NULL
4, ""
1, Hello
2, World

　いずれの方法がも一長一短ですので、適宜適した方法を使うことになります。

【MySQL】【PostgreSQL】ORDER BY時に null を上にするか下にするかを制御する

【CentOS】MySQLインストール時に’Data::Dumper’エラーが出た時の対処法

【Docker】【MySQL】MySQL イメージのコンテナを初期起動した際に自動で *.sql ファイル内の SQL 文を実行する

株式会社シーポイントラボ