SQLのORDER BYでNULL値を自由自在に操る！IS NULLを使ったソートテクニック徹底解説

はじめに：ORDER BYとNULL値の意外な落とし穴

SQLでデータを並び替える際に使うORDER BY句は、非常に頻繁に利用される基本的な機能です。しかし、ソート対象のカラムにNULL値が含まれる場合、RDBMS（リレーショナルデータベース管理システム）の種類によってその並び順が異なるため、意図しない結果になることがあります。

例えば、在庫数を昇順で並び替えたいのに、在庫不明（NULL）の商品が先頭に来たり、逆に末尾に来たりと、システムごとに挙動が変わるため、アプリケーションの移植性や期待通りの表示に問題が生じることがあります。

本記事では、このNULL値のソート問題を解決するために、SQLの標準的な機能であるIS NULLとCASE式を組み合わせたテクニックを詳しく解説します。これにより、どのRDBMSでも一貫してNULL値を先頭または末尾に配置し、データを思い通りに並び替える方法を習得できます。

RDBMSごとのNULL値ソートのデフォルト挙動

まずは、RDBMSがデフォルトでNULL値をどのように扱うかを見てみましょう。主要なRDBMSでは、以下のような傾向があります。

• MySQL, SQL Server: NULLを最小値として扱います。ASC（昇順）では先頭に、DESC（降順）では末尾に表示されます。
• PostgreSQL, Oracle: NULLを最大値として扱います。ASC（昇順）では末尾に、DESC（降順）では先頭に表示されます。

この違いが、クロスプラットフォーム開発や、特定のRDBMSから別のRDBMSへの移行時に問題となることがあります。

サンプルデータの準備

解説のために、以下のようなproductsテーブルとサンプルデータを用意します。stock_quantityカラムにはNULL値が含まれています。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT, -- 在庫数はNULLの場合がある
    last_updated DATE
);

INSERT INTO products (product_id, product_name, stock_quantity, last_updated) VALUES
(1, 'リンゴ', 100, '2023-01-15'),
(2, 'バナナ', NULL, '2023-01-10'), -- 在庫不明
(3, 'ミカン', 50, '2023-01-20'),
(4, 'ブドウ', NULL, '2023-01-18'), -- 在庫不明
(5, 'イチゴ', 200, '2023-01-22'),
(6, 'メロン', 10, '2023-01-12'),
(7, 'ピーチ', NULL, '2023-01-25'); -- 在庫不明

デフォルトのソート結果を確認

stock_quantityを昇順で並び替えてみましょう。

SELECT product_id, product_name, stock_quantity
FROM products
ORDER BY stock_quantity ASC;

MySQL / SQL Server での実行結果例:

product_id | product_name | stock_quantity
-----------+--------------+---------------
         2 | バナナ       |           NULL
         4 | ブドウ       |           NULL
         7 | ピーチ       |           NULL
         6 | メロン       |             10
         3 | ミカン       |             50
         1 | リンゴ       |            100
         5 | イチゴ       |            200

PostgreSQL / Oracle での実行結果例:

product_id | product_name | stock_quantity
-----------+--------------+---------------
         6 | メロン       |             10
         3 | ミカン       |             50
         1 | リンゴ       |            100
         5 | イチゴ       |            200
         2 | バナナ       |           NULL
         4 | ブドウ       |           NULL
         7 | ピーチ       |           NULL

ご覧の通り、NULL値の並び順がRDBMSによって異なることがわかります。

NULL値を先頭に指定して並び替える

NULL値を常に先頭に表示したい場合は、CASE式とIS NULLを組み合わせたソートキーをORDER BY句の最初に指定します。

考え方としては、stock_quantityがNULLであれば「0」、そうでなければ「1」という値を一時的に生成し、その値でソートします。数値の「0」は「1」よりも小さいので、NULL値が先頭に来るようになります。

SELECT product_id, product_name, stock_quantity
FROM products
ORDER BY
    CASE WHEN stock_quantity IS NULL THEN 0 ELSE 1 END ASC, -- NULLなら0、それ以外は1としてソート
    stock_quantity ASC; -- その後、本来のカラムでソート

実行結果:

product_id | product_name | stock_quantity
-----------+--------------+---------------
         2 | バナナ       |           NULL
         4 | ブドウ       |           NULL
         7 | ピーチ       |           NULL
         6 | メロン       |             10
         3 | ミカン       |             50
         1 | リンゴ       |            100
         5 | イチゴ       |            200

これで、どのRDBMSでもNULL値が先頭に表示されるようになりました。stock_quantityがNULLでない行は、CASE式の結果がすべて「1」となるため、その後に続くstock_quantity ASCによって通常の昇順ソートが適用されます。

NULL値を末尾に指定して並び替える

次に、NULL値を常に末尾に表示したい場合です。先ほどのCASE式のロジックを逆にします。

stock_quantityがNULLであれば「1」、そうでなければ「0」という値を一時的に生成し、その値でソートします。数値の「0」は「1」よりも小さいので、NULLでない値が先頭に来て、NULL値が末尾に来るようになります。

SELECT product_id, product_name, stock_quantity
FROM products
ORDER BY
    CASE WHEN stock_quantity IS NULL THEN 1 ELSE 0 END ASC, -- NULLなら1、それ以外は0としてソート
    stock_quantity ASC; -- その後、本来のカラムでソート

実行結果:

product_id | product_name | stock_quantity
-----------+--------------+---------------
         6 | メロン       |             10
         3 | ミカン       |             50
         1 | リンゴ       |            100
         5 | イチゴ       |            200
         2 | バナナ       |           NULL
         4 | ブドウ       |           NULL
         7 | ピーチ       |           NULL

この方法で、NULL値が末尾に統一されました。

補足：RDBMS固有の『NULLS FIRST / NULLS LAST』句

PostgreSQLやOracleなどの一部のRDBMSでは、より簡潔にNULL値の並び順を指定できる拡張機能があります。

• NULLS FIRST: NULL値を先頭に配置します。
• NULLS LAST: NULL値を末尾に配置します。

例（PostgreSQL / Oracle）:

NULL値を先頭にしたい場合:

SELECT product_id, product_name, stock_quantity
FROM products
ORDER BY stock_quantity ASC NULLS FIRST;

NULL値を末尾にしたい場合:

SELECT product_id, product_name, stock_quantity
FROM products
ORDER BY stock_quantity ASC NULLS LAST;

これらの句は非常に便利ですが、MySQLやSQL Serverではサポートされていません。そのため、汎用性という観点ではCASE式を使った方法が推奨されます。

NULL値を考慮した複合ソート

複数のカラムでソートする場合でも、このCASE式を使ったテクニックは応用できます。

例えば、「在庫数で昇順ソートし、NULLは末尾に。在庫数が同じ（またはNULL同士）の場合は、商品名で昇順ソートする」といった要件です。

SELECT product_id, product_name, stock_quantity, last_updated
FROM products
ORDER BY
    CASE WHEN stock_quantity IS NULL THEN 1 ELSE 0 END ASC, -- NULLを末尾に
    stock_quantity ASC, -- 在庫数で昇順
    product_name ASC; -- 在庫数が同じなら商品名で昇順

実行結果:

product_id | product_name | stock_quantity | last_updated
-----------+--------------+----------------+-------------
         6 | メロン       |             10 | 2023-01-12
         3 | ミカン       |             50 | 2023-01-20
         1 | リンゴ       |            100 | 2023-01-15
         5 | イチゴ       |            200 | 2023-01-22
         2 | バナナ       |           NULL | 2023-01-10
         4 | ブドウ       |           NULL | 2023-01-18
         7 | ピーチ       |           NULL | 2023-01-25

このように、CASE式を最初のソートキーとして利用することで、複雑なソート要件にも柔軟に対応できます。

まとめ

本記事では、SQLのORDER BY句でNULL値の並び順を制御する実践的なテクニックを解説しました。

• RDBMSによってNULL値のデフォルトソート挙動が異なることを理解しました。
• CASE WHEN column IS NULL THEN 0 ELSE 1 END ASC を使用することで、NULL値を先頭に配置できます。
• CASE WHEN column IS NULL THEN 1 ELSE 0 END ASC を使用することで、NULL値を末尾に配置できます。
• これらのCASE式は、どのRDBMSでも利用できる汎用的な解決策です。
• PostgreSQLやOracleではNULLS FIRST / NULLS LAST句も利用できますが、汎用性ではCASE式に軍配が上がります。
• 複合ソートにおいても、このテクニックを応用できることを確認しました。

NULL値のハンドリングはデータベース操作において非常に重要です。今回ご紹介したIS NULLとCASE式を組み合わせたソートテクニックは、現場で遭遇する様々な並び替えの課題を解決する強力な武器となるでしょう。ぜひご自身のプロジェクトで活用してみてください。