SQLSERVERのNULLのORDER BY

牧野 (2011年8月11日 18:11)
ソフトウエア開発部

みなさん、こんにちは。

データベースからデータを取得するとき、
データの並び順は常に気にしなければいけないものです。

もれなくデータが登録されていれば、
並べ替えも簡単に行えるのですが、
場合によってはNULLのデータ（値が登録されていない）が存在することがあります。

システムの要件によってNULL値を最初に持ってくるか、
或いは最後に持ってくるか、というのは変わってきます。

SQLSERVERでは、ORDER BY句の対象となるデータにNULL値が含まれていると、
まず先頭にNULL値を集め、その後ろに対象項目の値の並べ替えが行われます。

簡単にNULLの扱いを切り替えられると良いのですが、
SQLSERVERでは少々面倒なSQLを書く必要があります。

SELECT [column_name1]
     , [column_name2]
     , [column_name3]
  FROM [table_name1]
 ORDER BY CASE WHEN [column_name1] IS NULL THEN 1
               ELSE 0
          END ASC
     , [column_name1]

上記SQLのように記述し、
[column_name1]の値がNULLであれば1を返し、
[column_name1]の値がNULLでなければ0を返すことで、
返された数字を照準で並び替えたときにNULLのデータが後方に集まります。

しかしこれで上手くいかないケースも存在します。
それは、上記の方法に加えて、UNIONを行うSQLが書かれている場合です。

UNIONを行ってしまうとORDER BY句にCASE文が使えなくなってしまいます。
そのため、一例ですが、UNIONするSQL全体をサブクエリ化して、
SELECT文を1枚かませてることで、ORDER BYをかけることができます。

SELECT *
  FROM (
       SELECT [column_name1]
            , [column_name2]
            , [column_name3]
         FROM [table_name1]
        UNION ALL
       SELECT [column_name1]
            , [column_name2]
            , [column_name3]
         FROM [table_name2]
       ) AS [union_table_name]
 ORDER BY CASE WHEN [column_name1] IS NULL THEN 1
               ELSE 0
          END
     , [column_name1]

また、SELECTで取得する項目を増やすことが許される場合は、
ORDER BYで使用したCASE文を項目として取得するようにすれば、
並べ替えをかけることができます。

SELECT [column_name1]
     , [column_name2]
     , [column_name3]
     , CASE WHEN [column_name1] IS NULL THEN 1
            ELSE 0
       END AS [column_name4]
  FROM [table_name1]
 UNION ALL
SELECT [column_name1]
     , [column_name2]
     , [column_name3]
     , CASE WHEN [column_name1] IS NULL THEN 1
            ELSE 0
       END AS [column_name4]
  FROM [table_name2]
 ORDER BY [column_name4]
     , [column_name1]

ちなみに、ORACLEはORDER BY のオプション指定により簡単に切り替えることができます。
例：NULLを最後にする場合

SELECT [column_name1]
     , [column_name2]
     , [column_name3]
  FROM [table_name1]
 ORDER BY [column_name1] ASC NULLS LAST

システムのトラブル解決を承ります。すぐにシステムリフォームを体験できますシステム診断・システムリフォームのお申し込みはこちらから。

SQLSERVERのNULLのORDER BY

システム開発のアイロベックスは、創業23年を迎える
プロフェッショナル集団です。