【SQL Server裏技】NULLじゃない列だけをスマートに抽出する方法

SQL Server で列が NULL の場合に列を選択する

SQL Server で、ある列が NULL の場合に別の列を選択することは、さまざまな状況で役立ちます。例えば、レポート作成時に欠損データの代わりにデフォルト値を表示したり、分析を簡素化するために NULL 値を除外したりする場合などに便利です。

方法

この目的を達成するには、主に以下の 3 つの方法があります。

1. ISNULL関数

   ISNULL関数 は、指定された値が NULL であるかどうかを確認し、NULL の場合は別の値を返す関数です。構文は以下の通りです。

   ISNULL(expression, replacement_value)
   

   • expression: チェックする値
   • replacement_value: NULL の場合に返す値

   例：顧客名 列が NULL の場合に デフォルト顧客名 を返す

   SELECT ISNULL(顧客名, 'デフォルト顧客名') AS 顧客名
   FROM顧客テーブル
   

2. COALESCE関数

   COALESCE関数 は、指定された引数のうち、最初に NULL でない値を返します。構文は以下の通りです。

   COALESCE(expression1, expression2, ..., expressionN)
   

   • expression1, expression2, ..., expressionN: チェックする値を順番に列挙

   SELECT COALESCE(顧客名, 氏名) AS 顧客名
   FROM顧客テーブル
   

3. CASE式

   CASE式 は、条件に応じてさまざまな値を返す式です。構文は以下の通りです。

   CASE
       WHEN condition1 THEN value1
       WHEN condition2 THEN value2
       ...
       ELSE default_value
   END
   

   • condition1, condition2, ...: チェックする条件
   • value1, value2, ...: 各条件に一致した場合に返す値
   • default_value: すべての条件に一致しない場合に返す値

   例：顧客名 列が NULL の場合に デフォルト顧客名 を返し、顧客名 列が空文字の場合は 無名 を返す

   SELECT
       CASE
           WHEN 顧客名 IS NULL THEN 'デフォルト顧客名'
           WHEN 顧客名 = '' THEN '無名'
           ELSE 顧客名
       END AS 顧客名
   FROM顧客テーブル
   

• 上記以外にも、NULLIF 関数や CASE WHEN 構文と LAG() 関数を組み合わせて使用する方法などもあります。
• 目的や状況に応じて適切な方法を選択してください。
• どの方法を使用する場合でも、NULL 値の処理方法を明確に示すことが重要です。

ISNULL関数を使用する場合

-- 顧客名列がNULLの場合にデフォルト顧客名を返す
SELECT
    顧客ID,
    ISNULL(顧客名, 'デフォルト顧客名') AS 顧客名,
    氏名
FROM 顧客テーブル

説明:

• ISNULL(顧客名, 'デフォルト顧客名') は、顧客名 列が NULL の場合は 'デフォルト顧客名' を返し、NULL ではない場合は 顧客名 列の値をそのまま返します。

COALESCE関数を使用する場合

-- 顧客名列がNULLの場合に氏名を返す
SELECT
    顧客ID,
    COALESCE(顧客名, 氏名) AS 顧客名,
    氏名
FROM 顧客テーブル

• COALESCE(顧客名, 氏名) は、顧客名 列が NULL でない場合は 顧客名 列の値を返し、NULL の場合は 氏名 列の値を返します。

CASE式を使用する場合

-- 顧客名列がNULLの場合にデフォルト顧客名を、空文字の場合は無名を返す
SELECT
    顧客ID,
    CASE
        WHEN 顧客名 IS NULL THEN 'デフォルト顧客名'
        WHEN 顧客名 = '' THEN '無名'
        ELSE 顧客名
    END AS 顧客名,
    氏名
FROM 顧客テーブル

• CASE 式は、顧客名 列が NULL の場合は 'デフォルト顧客名', 空文字の場合は '無名', それ以外の場合は 顧客名 列の値を返します。

NULLIF関数 は、2 つの値を比較し、等しい場合は NULL を返す関数です。構文は以下の通りです。

NULLIF(expression1, expression2)

• expression1: 比較する値1

例：顧客名 列と 氏名 列が等しい場合に NULL を返し、そうでない場合は 顧客名 列を返す

SELECT
    顧客ID,
    NULLIF(顧客名, 氏名) AS 顧客名,
    氏名
FROM 顧客テーブル

LAG関数と CASE WHEN 構文

LAG関数 は、前の行の値を取得する関数です。これを CASE WHEN 構文と組み合わせることで、前の行の値が NULL の場合に現在の行の値を選択することができます。

例：顧客名 列が前の行の 顧客名 列と同じでかつ NULL の場合に 氏名 列を返す

SELECT
    顧客ID,
    CASE
        WHEN 顧客名 = LAG(顧客名) OVER (ORDER BY 顧客ID) AND 顧客名 IS NULL THEN 氏名
        ELSE 顧客名
    END AS 顧客名,
    氏名
FROM 顧客テーブル

サブクエリ

サブクエリを使用して、条件に一致する行を別のテーブルから取得することもできます。

例：顧客名 列が NULL の場合に、顧客別名テーブル から 顧客別名 列を取得する

SELECT
    顧客ID,
    (
        SELECT 顧客別名
        FROM 顧客別名テーブル
        WHERE 顧客ID = c.顧客ID
    ) AS 顧客名,
    氏名
FROM 顧客テーブル AS c

列セット

SQL Server 2016 以降では、列セット という機能を使用して、スパース列の値を効率的に処理することができます。列セットを使用すると、NULL 以外の値のみを含む新しい列を作成できます。

例：顧客名 列と 氏名 列がスパース列である場合、NULL 以外の値のみを含む 顧客名_氏名 列を作成する

SELECT
    顧客ID,
    c.顧客名,
    c.氏名,
    c.*
FROM 顧客テーブル AS c
CROSS APPLY
    SPARSE_COLUMNSET(c, '顧客名', '氏名') AS cs

注意事項

• 上記の方法は、それぞれ異なる状況で役立ちます。
• 複雑なクエリを使用する場合は、パフォーマンスと可読性を考慮する必要があります。