null 値の処理

[アーティクル]
05/10/2023

列の値が不明または欠落している場合は、リレーショナルデータベースの NULL 値が使用されます。 null は、空の文字列 (文字型または日時データ型の場合) でも、0 値 (数値データ型の場合) でもありません。 ANSI SQL-92 の規格では、すべての null が一貫して処理されるように、すべてのデータ型で同じである必要があると規定されています。 System.Data.SqlTypes 名前空間では、INullable インターフェイスを実装することによって null セマンティクスが提供されます。 System.Data.SqlTypes 内の各データ型には、それぞれ独自に IsNull プロパティと Null 値があり、データ型のインスタンスに割り当てることができます。

Note

.NET Framework version 2.0 では、null 許容値型がサポートされました。この型を使用することで、値型を拡張して基になる型のすべての値を表すことができます。これらの CLR null 許容値型は、Nullable 構造体のインスタンスを表します。この機能は、値の型がボックスまたはアンボックスされるときに特に有効であり、オブジェクト型との互換性が強化されます。 ANSI SQL の null は null 参照 (Visual Basic では Nothing) と動作が異なるため、CLR null 許容値型は null のデータベースへの格納を意図したものではありません。データベースの ANSI SQL NULL 値を操作するには、System.Data.SqlTypes ではなく Nullable NULL を使用します。 Visual Basic での CLR null 許容値型の操作の詳細については「null 許容値型」を、C# については「null 許容値型」を参照してください。

NULL および 3 つの値を持つロジック

列定義で null 値を許可すると、アプリケーションに 3 値ロジックが導入されます。比較では、次の 3 つの状態のいずれかに評価されます。

True
False
Unknown

null は不明と見なされるため、2 つの null 値を互いに比較しても、値が等しいとは見なされません。算術演算子を使用した式では、オペランドのいずれかが null である場合は結果も null になります。

null と SqlBoolean

任意の System.Data.SqlTypes 間の比較により、SqlBoolean が返されます。各 SqlType に対する IsNull 関数では SqlBoolean が返され、null 値の確認に使用できます。次の真理値表は、null 値がある場合に AND、OR、NOT 演算子がどのように機能するかを示しています。 (T=true、F=false、U=不明または null。)

Truth Table

ANSI_NULLS オプションについて

System.Data.SqlTypes では、ANSI_NULLS オプションが SQL Server で設定された場合と同じセマンティクスになります。すべての算術演算子 (+、-、*、/、%)、ビット演算子 (~、、|)、およびほとんどの関数では、プロパティ IsNull を除き、オペランドまたは引数が null であった場合に null を返します。

ANSI SQL-92 標準では、WHERE 句で columnName = NULL とすることは認められていません。 SQL Server では、ANSI_NULLS オプションによって、データベース内の既定の null 値の許容と null 値に対する比較の評価の両方が制御されます。 ANSI_NULLS が有効になっている (既定) 場合、null 値をテストする式で IS NULL 演算子を使用する必要があります。たとえば次の比較では、ANSI_NULLS がオンである場合、常に不明となります。

colname > NULL

また、null 値を含む変数との比較でも不明となります。

colname > @MyVariable

NULL 値をテストするには、IS NULL または IS NOT NULL 述語を使用します。これにより WHERE 句が複雑になる場合があります。たとえば、AdventureWorks Customer テーブル内の TerritoryID 列では null 値が許可されています。 SELECT ステートメントによってその他の値と合わせて NULL 値もテストされる場合は、IS NULL 述語を含める必要があります。

SELECT CustomerID, AccountNumber, TerritoryID  
FROM AdventureWorks.Sales.Customer  
WHERE TerritoryID IN (1, 2, 3)  
   OR TerritoryID IS NULL

SQL Server で ANSI_NULLS を無効にすると、等値演算子を使用して null と比較する式を作成できます。ただし、別の接続ではこの接続の null オプションの設定を回避することはできません。 IS NULL を使用した null 値のテストは、接続の ANSI_NULLS の設定に関係なく常に機能します。

DataSet では、ANSI_NULLS のオフ設定はサポートされていません。System.Data.SqlTypes における null 値の処理については、常に ANSI SQL-92 標準に準拠しています。

NULL 値の割り当て

null 値は特殊であるため、その格納や割り当てのセマンティクスはシステムおよびストレージシステムの種類によって異なります。 Dataset は、異なる種類のストレージシステムで使用されるように設計されています。

このセクションでは、異なる種類のシステムにある DataRow の DataColumn に null 値を割り当てるための null セマンティクスについて説明します。

DBNull.Value
この割り当ては、任意の型の DataColumn で有効です。その型が INullable を実装している場合、DBNull.Value は、厳密に型指定された適切な null 値に強制的に変換されます。

SqlType.Null
すべての System.Data.SqlTypes データ型で INullable を実装します。暗黙的なキャスト演算子を使用して、厳密に型指定された null 値を列のデータ型に変換できる場合は、割り当てが行われます。そうでない場合は、無効なキャスト例外がスローされます。

null
'null' が特定の DataColumn データ型で有効な値である場合は、INullable 型 (SqlType.Null) に関連付けられた適切な DbNull.Value または Null に強制的に変換されます。

derivedUdt.Null
UDT 列の場合、null 値は常に DataColumn に関連付けられた型に基づいて格納されます。 DataColumn に関連付けられた UDT が INullable を実装しておらず、そのサブクラスで実装している場合を考えてみます。この場合、派生クラスに関連付けられた厳密に型指定された null 値が割り当てられていれば、null ストレージが常に DataColumn のデータ型と一致するため、型指定されていない DbNull.Value として格納されます。

Note

Nullable<T> または Nullable 構造体は、現在 DataSet ではサポートされていません。

任意の System.Data.SqlTypes インスタンスの既定値は NULL です。

System.Data.SqlTypes の null 値は型固有であり、DbNull などの 1 つの値で表すことはできません。 null 値を確認するには、IsNull プロパティを使用します。

null 値は次のコード例に示すように、DataColumn に割り当てることができます。 null 値は例外をトリガーすることなく、SqlTypes 変数に直接割り当てることができます。

例

次のコード例では、SqlInt32 と SqlString として定義された 2 つの列を含む DataTable を作成します。このコードでは、既知の値の 1 行と null 値の 1 行を追加し、DataTable を反復処理します。これによって変数に値を割り当て、コンソールウィンドウに出力を表示します。

static void WorkWithSqlNulls()
{
    DataTable table = new();

    // Specify the SqlType for each column.
    DataColumn idColumn =
        table.Columns.Add("ID", typeof(SqlInt32));
    DataColumn descColumn =
        table.Columns.Add("Description", typeof(SqlString));

    // Add some data.
    DataRow nRow = table.NewRow();
    nRow["ID"] = 123;
    nRow["Description"] = "Side Mirror";
    table.Rows.Add(nRow);

    // Add null values.
    nRow = table.NewRow();
    nRow["ID"] = SqlInt32.Null;
    nRow["Description"] = SqlString.Null;
    table.Rows.Add(nRow);

    // Initialize variables to use when
    // extracting the data.
    SqlBoolean isColumnNull = false;
    SqlInt32 idValue = SqlInt32.Zero;
    SqlString descriptionValue = SqlString.Null;

    // Iterate through the DataTable and display the values.
    foreach (DataRow row in table.Rows)
    {
        // Assign values to variables. Note that you
        // do not have to test for null values.
        idValue = (SqlInt32)row["ID"];
        descriptionValue = (SqlString)row["Description"];

        // Test for null value in ID column.
        isColumnNull = idValue.IsNull;

        // Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}",
            isColumnNull, idValue, descriptionValue);
        Console.WriteLine();
    }

Private Sub WorkWithSqlNulls()
    Dim table As New DataTable()

    ' Specify the SqlType for each column.
    Dim idColumn As DataColumn = _
      table.Columns.Add("ID", GetType(SqlInt32))
    Dim descColumn As DataColumn = _
      table.Columns.Add("Description", GetType(SqlString))

    ' Add some data.
    Dim row As DataRow = table.NewRow()
    row("ID") = 123
    row("Description") = "Side Mirror"
    table.Rows.Add(row)

    ' Add null values.
    row = table.NewRow()
    row("ID") = SqlInt32.Null
    row("Description") = SqlString.Null
    table.Rows.Add(row)

    ' Initialize variables to use when
    ' extracting the data.
    Dim isColumnNull As SqlBoolean = False
    Dim idValue As SqlInt32 = SqlInt32.Zero
    Dim descriptionValue As SqlString = SqlString.Null

    ' Iterate through the DataTable and display the values.
    For Each row In table.Rows
        ' Assign values to variables. Note that you 
        ' do not have to test for null values.
        idValue = CType(row("ID"), SqlInt32)
        descriptionValue = CType(row("Description"), SqlString)

        ' Test for null value with ID column
        isColumnNull = idValue.IsNull

        ' Display variable values in console window.
        Console.Write("isColumnNull={0}, ID={1}, Description={2}", _
          isColumnNull, idValue, descriptionValue)
        Console.WriteLine()
    Next row
End Sub

この例を実行すると、次の結果が表示されます。

isColumnNull=False, ID=123, Description=Side Mirror  
isColumnNull=True, ID=Null, Description=Null

複数列 (行) の割り当て

DataTable.Add、DataTable.LoadDataRow、または行にマップされる ItemArray を受け入れる他の API では、DataColumn の既定値に 'null' をマップします。配列内のオブジェクトに DbNull.Value またはその厳密に型指定された同等の値が含まれている場合は、上記と同じ規則が適用されます。

さらに、DataRow.["columnName"] null 値割り当てのインスタンスには次の規則が適用されます。

"既定" の値は DbNull.Value です。ただし、それが厳密に型指定された適切な null 値となる、厳密に型指定された null 列は例外です。
XML ファイルへのシリアル化中に null 値が ("xsi:nil" として) 書き出されることはありません。
既定値を含む null 以外の値はすべて、XML へのシリアル化中に常に書き出されます。これは、null 値 (xsi:nil) が明示的であり、既定値が暗黙的である XSD/XML セマンティクスとは異なります (XML にない場合、検証パーサーが関連付けられた XSD スキーマから取得します)。 DataTable ではこの逆になり、null 値が暗黙的であり、既定値が明示的です。
XML 入力から読み取られた各行で欠落している列値にはすべて、NULL が割り当てられます。 NewRow または同様のメソッドを使用して作成された行には、DataColumn の既定値が割り当てられます。
IsNull メソッドは、true と DbNull.Value のどちらに対しても INullable.Null を返します。

NULL 値と SqlTypes および CLR 型との比較

null 値を比較する場合は、Equals メソッドが System.Data.SqlTypes の null 値を評価する方法と、CLR 型を操作する方法の違いを理解することが重要です。 System.Data.SqlTypesEquals メソッドはすべて、null 値の評価にデータベースセマンティクスを使用します。値の一方または両方が null である場合は、その比較によって null が得られます。これに対して、2 つの System.Data.SqlTypes に対して CLR Equals メソッドを使用すると、両方が null である場合に true が得られます。これは、CLR String.Equals メソッドなどのインスタンスメソッドの使用と、静的/共有メソッド SqlString.Equals の使用の違いを反映しています。

次の例は、SqlString.Equals メソッドと String.Equals メソッドにそれぞれ null 値のペアを渡し、次に空の文字列のペアを渡した場合の各メソッドの結果の違いを示しています。

    static void CompareNulls()
    {
        // Create two new null strings.
        SqlString a = new();
        SqlString b = new();

        // Compare nulls using static/shared SqlString.Equals.
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b));

        // Compare nulls using instance method String.Equals.
        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two nulls={0}", StringEquals(a, b));

        // Make them empty strings.
        a = "";
        b = "";

        // When comparing two empty strings (""), both the shared/static and
        // the instance Equals methods evaluate to true.
        Console.WriteLine();
        Console.WriteLine("SqlString.Equals shared/static method:");
        Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b));

        Console.WriteLine();
        Console.WriteLine("String.Equals instance method:");
        Console.WriteLine("  Two empty strings={0}", StringEquals(a, b));
    }

    static string SqlStringEquals(SqlString string1, SqlString string2)
    {
        // SqlString.Equals uses database semantics for evaluating nulls.
        var returnValue = SqlString.Equals(string1, string2).ToString();
        return returnValue;
    }

    static string StringEquals(SqlString string1, SqlString string2)
    {
        // String.Equals uses CLR type semantics for evaluating nulls.
        var returnValue = string1.Equals(string2).ToString();
        return returnValue;
    }
}

Private Sub CompareNulls()
    ' Create two new null strings.
    Dim a As New SqlString
    Dim b As New SqlString

    ' Compare nulls using static/shared SqlString.Equals.
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two nulls={0}", SqlStringEquals(a, b))

    ' Compare nulls using instance method String.Equals.
    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two nulls={0}", StringEquals(a, b))

    ' Make them empty strings.
    a = ""
    b = ""

    ' When comparing two empty strings (""), both the shared/static and
    ' the instance Equals methods evaluate to true.
    Console.WriteLine()
    Console.WriteLine("SqlString.Equals shared/static method:")
    Console.WriteLine("  Two empty strings={0}", SqlStringEquals(a, b))

    Console.WriteLine()
    Console.WriteLine("String.Equals instance method:")
    Console.WriteLine("  Two empty strings={0}", StringEquals(a, b))
End Sub

Private Function SqlStringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' SqlString.Equals uses database semantics for evaluating nulls.
    Dim returnValue As String = SqlString.Equals(string1, string2).ToString()
    Return returnValue
End Function

Private Function StringEquals(ByVal string1 As SqlString, _
    ByVal string2 As SqlString) As String

    ' String.Equals uses CLR type semantics for evaluating nulls.
    Dim returnValue As String = string1.Equals(string2).ToString()
    Return returnValue
End Function

このコードを実行すると、次の出力が生成されます。

SqlString.Equals shared/static method:  
  Two nulls=Null  
  
String.Equals instance method:  
  Two nulls=True  
  
SqlString.Equals shared/static method:  
  Two empty strings=True  
  
String.Equals instance method:  
  Two empty strings=True