Loading article…
SQLデータベースクエリ言語では、nullまたはNULLは、データベースにデータ値が存在しないことを示すために使用される特別なマーカーです。リレーショナルデータベースモデルの考案者であるE. F. Coddによって導入されたSQL nullは、すべての真のリレーショナルデータベース管理システム（RDBMS）が「欠落情報および適用不可能な情報」の表現をサポートするという要件を満たすために用いられます。Coddはまた、データベース理論においてnullを表すためにギリシャ文字の小文字のオメガ（ω）記号の使用も導入しました。SQLでは、このマーカーを識別するために使用される予約語です。NULL
null は0という値と混同してはいけません。null は値がないことを示すものであり、ゼロ値とは異なります。例えば、「アダムは何冊の本を所有していますか？」という質問の場合、答えは「ゼロ」（本の数がゼロであることがわかっている）または「null」（本の数がわからない）のいずれかになります。データベーステーブルでは、この答えを報告する列は最初は値なし（null でマーク）で始まり、アダムが本を所有していないことが確認されるまで、値 0 で更新されることはありません。
SQLでは、nullは値ではなくマーカーです。この用法は、ほとんどのプログラミング言語とは異なります。プログラミング言語では、参照のnull値は、どのオブジェクトも指していないことを意味します。
EF Codd は、 1975 年にACM - SIGMODの FDT Bulletinに掲載された論文で、リレーショナル モデルにおける欠損データの表現方法として null について言及しました。SQL で採用されている null のセマンティクスと最もよく関連付けられる Codd の論文は、1979 年にACM Transactions on Database Systemsに掲載された論文で、この論文ではリレーショナル モデル / Tasmaniaも紹介されていますが、後者の論文の他の提案の多くは不明瞭なままです。1979 年の論文のセクション 2.3 では、算術演算における null 伝播のセマンティクスと、 null と比較する際に三値論理を使用する比較について詳しく説明しています。また、他の集合演算における null の扱いについても詳しく説明しています (後者の問題は今日でも議論の的となっています)。データベース理論の分野では、Codd (1975 年、1979 年) の元の提案は現在「Codd テーブル」と呼ばれています。[ 1 ]コッドは後に、 Computerworld誌に掲載された1985年の2部構成の記事で、すべてのRDBMSが欠損データを示すためにNullをサポートする必要があるという自身の要求を改めて強調した。[ 2 ] [ 3 ]
1986 年の SQL 標準は、基本的にIBM System Rでの実装プロトタイプの後、コッドの提案を採用しました。ドン・チェンバリンは、NULL （重複行と並んで）が SQL の最も議論を呼ぶ機能の 1 つであることを認識していましたが、SQL における NULL の設計を擁護し、欠損情報に対するシステム サポートの最も安価な形式であり、プログラマが多くの重複するアプリケーション レベルのチェックを回避できる (半述語問題を参照) と同時に、データベース設計者が望む場合は NULL を使用しないオプションを提供するという実用的な議論を持ち出しました。たとえば、よく知られている異常 (この記事のセマンティクスのセクションで説明) を回避するためです。チェンバリンはまた、欠損値機能を提供するだけでなく、NULL の実践的な経験から、特定のグループ化構造や外部結合など、NULL に依存する他の言語機能も生まれたと主張しました。最後に、彼は、実際には、既存のスキーマが本来の意図を超えて進化する必要がある場合に、欠損値ではなく適用できない情報をコード化することで、スキーマを迅速に修正する方法としてNullが使用されることもあると主張した。たとえば、燃費列を持ちながら電気自動車を迅速にサポートする必要のあるデータベースなどである。[ 4 ]
コッドは1990年の著書『データベース管理のためのリレーショナルモデル、バージョン2』の中で、SQL標準で義務付けられている単一のNullは不十分であり、データが欠落している理由を示すために2つの別々のNull型マーカーに置き換えるべきだと指摘した。コッドの著書では、これら2つのNull型マーカーは「A値」と「I値」と呼ばれ、それぞれ「欠落しているが適用可能」と「欠落しているが適用不可」を表している。[ 5 ]コッドの提案では、SQLの論理システムを4値論理システムに対応できるように拡張する必要があった。この複雑さが増すため、異なる定義を持つ複数のNullというアイデアは、データベース実務者の間で広く受け入れられていない。しかし、これは活発な研究分野であり、多数の論文が今も発表されている。
null は、関連する3 値論理(3VL)、 SQL 結合での使用に関する特別な要件、集計関数や SQL グループ化演算子で必要とされる特別な処理のため、論争の的となり、議論の的となってきました。コンピュータ科学の教授である Ron van der Meyden 氏は、さまざまな問題を次のように要約しています。「SQL 標準の矛盾により、SQL における null の処理に直感的な論理的意味論を割り当てることは不可能です。」[ 1 ]これらの問題を解決するためにさまざまな提案がなされてきましたが、代替案の複雑さにより、広く採用されることはありませんでした。
Nullはデータ値ではなく、存在しない値を示すマーカーであるため、Nullに対して数学演算子を使用すると、Nullで表される未知の結果が得られます。[ 6 ]次の例では、10にNullを掛けるとNullになります。
10 * NULL -- 結果はNULLですこれにより、予期しない結果が生じる可能性があります。たとえば、Null をゼロで割ろうとすると、プラットフォームは期待される「データ例外-ゼロによる除算」をスローする代わりに Null を返す場合があります。[ 6 ]この動作は ISO SQL 標準では定義されていませんが、多くの DBMS ベンダーはこの操作を同様に扱います。たとえば、Oracle、PostgreSQL、MySQL Server、および Microsoft SQL Server プラットフォームはすべて、次の操作に対して Null の結果を返します。
NULL / 0SQLでよく使われる文字列連結操作も、オペランドの1つがNullの場合、Nullを返します。[ 7 ]||次の例は、SQL文字列連結演算子でNullを使用した場合に返されるNullの結果を示しています。
'Fish' || NULL || 'Chips' -- 結果はNULLこれはすべてのデータベース実装に当てはまるわけではありません。たとえば、Oracle RDBMSでは、NULLと空文字列は同じものとみなされるため、「Fish 」|| NULL || 「Chips」は「Fish Chips」になります。[ 8 ]
Null はどのデータ ドメインにも属さないため、「値」とはみなされず、未定義の値を示すマーカー (またはプレースホルダー) とみなされます。このため、Null との比較では True または False になることはなく、常に第 3 の論理結果である Unknown になります。[ 9 ]値 10 を Null と比較する以下の式の論理結果は Unknown です。
SELECT 10 = NULL -- 結果は不明ただし、Null に対する特定の操作では、存在しない値が操作の結果に関係ない場合、値を返すことがあります。次の例を考えてみましょう。
SELECT NULL OR TRUE -- 結果はTrueこの場合、OR演算の左側の値が不明であるという事実は関係ありません。なぜなら、左側の値に関係なく、OR演算の結果は真になるからです。
SQL は 3 つの論理結果を実装するため、SQL の実装では特殊な3 値論理 (3VL)を提供する必要があります。SQL の 3 値論理を規定する規則は、以下の表に示されています ( pとq は論理状態を表します) 。 [ 10 ] SQL が AND、OR、NOT に使用する真理値表は、Kleene と Łukasiewicz の 3 値論理の共通部分に対応しています (含意の定義が異なりますが、SQL ではそのような操作は定義されていません)。[ 11 ]
SQL の 3 値論理は、データ操作言語(DML) において、DML ステートメントおよびクエリの比較述語で発生します。この句により、DML ステートメントは、述語が True と評価される行のみに対して実行されます。述語が False または Unknown と評価される行は、、、またはDML ステートメントWHEREによって実行されず、クエリによって破棄されます。Unknown と False を同じ論理結果として解釈することは、Null を扱う際によく発生するエラーです。[ 10 ]次の簡単な例は、この誤謬を示しています。INSERTUPDATEDELETESELECT
SELECT * FROM t WHERE i = NULL ;上記のクエリ例では、i列とNullの比較結果が常にUnknownとなるため、論理的には常に0行が返されます。これは、 iがNullである行についても同様です。Unknownという結果によって、SELECTステートメントはすべての行を即座に破棄します。（ただし、実際には、一部のSQLツールはNullとの比較を使用して行を取得します。）
基本的な SQL 比較演算子は、Null と比較すると常に Unknown を返すため、SQL 標準では、Null に特化した 2 つの特別な比較述語が提供されています。andIS NULL述語(後IS NOT NULL置構文を使用) は、データが Null であるか否かをテストします。[ 12 ]
SQL 標準には、オプション機能 F571「真理値テスト」が含まれており、後置記法を使用する 3 つの追加の論理単項演算子 (実際には、構文の一部である否定を含めると 6 つ) が導入されています。それらの真理値表は次のとおりです。[ 13 ]
F571 機能は、SQL におけるブールデータ型の存在(この記事の後半で説明) とは直交しており、構文上の類似性にもかかわらず、F571 は言語にブール値や 3 値リテラルを導入するものではありません。F571 機能は、実際には1999 年にブールデータ型が標準に導入されるずっと前のSQL92 [ 14 ]に存在していました。ただし、F571 機能を実装しているシステムは少なく、PostgreSQL はその 1 つです。
SQLの3値論理の他の演算子にIS UNKNOWNを追加することで、SQLの3値論理は機能的に完全になります[ 15 ]。つまり、その論理演算子は（組み合わせて）考えられるあらゆる3値論理関数を表現できます。
F571機能をサポートしていないシステムでは、式pを不明にする可能性のあるすべての引数を調べて、それらの引数をIS NULLまたは他のNULL固有の関数でテストすることで、IS UNKNOWN pをエミュレートすることが可能ですが、これはより面倒になる可能性があります。
SQLの3値論理では、排中律（p OR NOT p）はすべてのpに対して真とは評価されません。より正確には、SQLの3値論理では、p OR NOT pはpが不明な場合にのみ不明となり、それ以外の場合は真となります。Nullとの直接比較では不明な論理値となるため、次のクエリは
SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 );SQL では、
SELECT * FROM stuff ;列 x に NULL が含まれている場合、2 番目のクエリは最初のクエリでは返されない行、つまり x が NULL であるすべての行を返します。古典的な二値論理では、排中律により WHERE 句の述語を簡略化、実際には削除することができます。SQL の 3VL に排中律を適用しようとすると、実質的に誤った二分法になります。2番目のクエリは実際には次のクエリと同等です。
SELECT * FROM stuff ; -- は (3VL のため) SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL ;と同等です。したがって、SQLの最初のステートメントを正しく簡略化するには、xがnullでないすべての行を返す必要があります。
SELECT * FROM stuff WHERE x IS NOT NULL ;上記を踏まえると、SQLのWHERE句では、排中律に似た恒真式が書けることに注意すべきである。IS UNKNOWN演算子が存在すると仮定すると、すべての述語pに対してp OR (NOT p ) OR ( p IS UNKNOWN)が真となる。論理学者の間では、これは排中律と呼ばれる。
SQL式の中には、誤ったジレンマが発生する箇所が分かりにくいものもあります。例えば、次のようになります。
SELECT 'ok' WHERE 1 NOT IN ( SELECT CAST ( NULL AS INTEGER )) UNION SELECT 'ok' WHERE 1 IN ( SELECT CAST ( NULL AS INTEGER ));引数セットに対する等価性の反復バージョンに変換されるため、行は生成されません。1 IN<>NULL は Unknown であり、1=NULL も Unknown です。（この例の CAST は、PostgreSQL などの一部の SQL 実装でのみ必要です。そうでない場合、型チェック エラーで拒否されます。多くのシステムでは、サブクエリで単純な SELECT NULL が機能します。）上記の欠落しているケースは、もちろん次のとおりです。
SELECT 'ok' WHERE ( 1 IN ( SELECT CAST ( NULL AS INTEGER ))) IS UNKNOWN ;結合は、WHERE句と同じ比較ルールを使用して評価されます。したがって、SQL結合条件でNULL許容列を使用する場合は注意が必要です。特に、NULLを含むテーブルは、それ自体の自然な自己結合とは等しくありません。つまり、関係代数における任意の関係Rに対して、SQL の自己結合は、どこかに Null を持つすべての行を除外します。[ 16 ]この動作の例は、Null の欠損値の意味論を分析するセクションで示されています。
SQLCOALESCE関数またはCASE式を使用すると、結合条件でNULL値の等価性を「シミュレート」できます。また、 IS NULLANDIS NOT NULL述語も結合条件で使用できます。次の述語は、値AとBの等価性をテストし、NULL値を等しいものとして扱います。
( A = B )または( AがNULLかつBがNULL )SQLには2種類の条件式があります。1つは「単純CASE」と呼ばれ、switch文のように動作します。もう1つは標準では「検索CASE」と呼ばれ、if...elseifのように動作します。
単純式は、DML句の Null に関する規則CASEと同じ規則に従って動作する暗黙的な等価比較を使用します。したがって、単純式ではNull の存在を直接チェックすることはできません。単純式で Null をチェックすると、常に次の例のように Unknown になります。WHERECASECASE
SELECT CASE i WHEN NULL THEN 'Is Null' -- これは決して返されませんWHEN 0 THEN 'Is Zero' -- i = 0 の場合に返されますWHEN 1 THEN 'Is One' -- i = 1 の場合に返されますEND FROM t ;式は、列iにどのような値が含まれていても (たとえ Null が含まれていても) i = NULLUnknown と評価されるため、文字列は決して返されません。'Is Null'
一方、「検索」式では、条件にCASE述語を使用できます。次の例は、検索式を使用して Null を適切にチェックする方法を示しています。IS NULLIS NOT NULLCASE
SELECT CASE WHEN i IS NULL THEN 'Null Result' -- i が NULL の場合に返されますWHEN i = 0 THEN 'Zero' -- i = 0 の場合に返されますWHEN i = 1 THEN 'One' -- i = 1 の場合に返されますEND FROM t ;検索式では、 iが Nullであるすべての行に対してCASE文字列が返されます。'Null Result'
DECODEOracleのSQL方言には、単純なCASE式の代わりに使える組み込み関数があり、2つのNULL値を等しいとみなします。
SELECT DECODE ( i , NULL , 'Null Result' , 0 , 'Zero' , 1 , 'One' ) FROM t ;最後に、これらの構文はすべて、一致するものが見つからない場合はNULLを返します。つまり、デフォルトELSE NULL句を持っています。
SQL/PSM (SQL Persistent Stored Modules) は、ステートメントなどの SQL の手続き型IF拡張機能を定義しています。しかし、主要な SQL ベンダーはこれまで独自の手続き型拡張機能を組み込んできました。ループや比較のための手続き型拡張機能は、DML ステートメントやクエリと同様の Null 比較ルールに基づいて動作します。次のコード断片は、ISO SQL 標準形式で、ステートメントでの Null 3VL の使用例を示していますIF。
iがNULLの場合、「結果は True」を選択します。 i がNULLでない場合、「結果は False」を選択します。それ以外の場合は、「結果は不明」を選択します。このIFステートメントは、真と評価される比較に対してのみ処理を実行します。偽または不明と評価されるステートメントについては、IFステートメントは制御をELSEIF句に渡し、最終的にELSE句に渡します。上記のコードの結果は常にメッセージになります。'Result is Unknown'これは、Nullとの比較は常に不明と評価されるためです。
T. ImielińskiとW. Lipski Jr. (1984) [ 17 ]の画期的な研究は、 Imieliński-Lipski 代数と呼ばれる欠損値意味論を実装するためのさまざまな提案の意図された意味論を評価するための枠組みを提供しました。このセクションは、「Alice」教科書の第 19 章にほぼ従っています。[ 18 ]同様の説明は、Ron van der Meyden のレビュー、§10.4 にも見られます。[ 1 ]
コッドテーブルなどの欠落情報を表す構造は、実際には、パラメータの可能なインスタンスごとに1つずつ、一連の関係を表すことを意図しています。コッドテーブルの場合、これはヌルを具体的な値に置き換えることを意味します。たとえば、
（コッド表のような）構成要素は、その構成要素に対して行われたクエリに対する任意の回答を、それが表す関係（構成要素のモデルと見なされる）に対する対応する任意のクエリに対する回答を得るために特定できる場合、（欠落情報の）強力な表現システムであると言われます。より正確には、q が（「純粋な」関係の）関係代数におけるクエリ式であり、 q が欠落情報を表すことを意図した構成要素へのその持ち上げである場合、強力な表現は、任意のクエリqと（表）構成要素Tに対して、q が構成要素へのすべての回答を持ち上げ、すなわち、次の性質を持ちます。
（上記は任意の数のテーブルを引数として取るクエリに対して成り立つ必要がありますが、この議論では1つのテーブルに限定すれば十分です。）明らかに、選択と射影をクエリ言語の一部とみなすと、コッドテーブルはこの強い特性を持ちません。たとえば、すべての回答は次のようになります。
SELECT * FROM Emp WHERE Age = 22 ;EmpH22のような関係が存在する可能性も考慮に入れるべきです。しかし、コッド表では「行数が0または1になる可能性のある結果」という論理和を表すことはできません。ただし、主に理論的な関心事である条件表（またはc-表）と呼ばれる手法は、そのような結果を表現できます。
条件列は、条件が偽の場合、行が存在しないと解釈されます。cテーブルの条件列の式は任意の命題論理式になり得るため、cテーブルが何らかの具体的な関係を表しているかどうかを判定するアルゴリズムはco-NP完全複雑度を持ち、実用上の価値はほとんどありません。
したがって、より弱い表現の概念が望ましい。イミエリンスキーとリプスキーは弱い表現の概念を導入した。これは基本的に、構成要素に対する（リフトされた）クエリが、確実な情報、つまり構成要素のすべての「可能世界」インスタンス化（モデル）に対して有効な場合にのみ表現を返すことを可能にする。具体的には、構成要素が弱い表現システムであるのは、
上記の式の右辺は確実な情報、つまりデータベース内の Null を置き換えるためにどのような値が使用されているかに関わらず、データベースから確実に抽出できる情報です。上で検討した例では、クエリ選択のすべての可能なモデル (つまり確実な情報) の共通部分が実際には空であることが容易にわかります。たとえば、(リフトされていない) クエリはリレーション EmpH37 に対して行を返さないためです。より一般的には、クエリ言語が射影、選択 (および列の名前変更) に制限されている場合、Codd テーブルは弱い表現システムであることが Imielinski と Lipski によって示されました。しかし、クエリ言語に結合またはユニオンを追加するとすぐに、次のセクションで示すように、この弱い特性さえも失われます。WHEREAge=22
前のセクションで扱ったのと同じCoddテーブルEmpに対して、次のクエリを実行してみましょう。
SELECT Name FROM Emp WHERE Age = 22 UNION SELECT Name FROM Emp WHERE Age <> 22 ;ハリエットの年齢にどのような具体的な値を選択しても、上記のクエリはEmpNULLのどのモデルでも名前の列全体を返しますが、(リフトされた)クエリをEmp自体に対して実行すると、ハリエットは常に欠落します。つまり、次のようになります。
このように、クエリ言語にUNIONが追加されると、Coddテーブルは欠落情報の弱い表現システムにもならず、つまり、それらに対するクエリは確実な情報をすべて報告することさえありません。このクエリでは、UNION on Nullsのセマンティクスは関係ありませんでした。2つのサブクエリの「忘れっぽい」性質だけで、上記のクエリがCoddテーブルEmpに対して実行されたときに、確実な情報の一部が報告されないことが保証されました。
自然結合の場合、特定の情報が一部のクエリで報告されない可能性があることを示すために必要な例は、少し複雑になります。次のテーブルを考えてみましょう。
そしてクエリ
SELECT F1 , F3 FROM ( SELECT F1 , F2 FROM J ) AS F12 NATURAL JOIN ( SELECT F2 , F3 FROM J ) AS F23 ;上記の現象の直感的な説明は、サブクエリ内の射影を表すコッドテーブルが、列 F12.F2 および F23.F2 の Null が実際にはテーブル J 内の元のコピーであるという事実を見失っているということです。この観察から、コッドテーブル (この例では正しく機能する) の比較的簡単な改善策として、単一の NULL シンボルの代わりに、スコーレム定数(定数関数でもあるスコーレム関数)、例えば ω 12および ω 22を使用することが考えられます。このようなアプローチは、v-テーブルまたはナイーブテーブルと呼ばれ、上記の c-テーブルよりも計算コストが低くなります。ただし、v-テーブルは選択で否定を使用しないクエリ (および集合差を使用しないクエリ) に対しては弱い表現にすぎないという意味で、不完全な情報に対する完全な解決策ではありません。このセクションで検討した最初の例では、否定選択句を使用しているため、v-テーブルクエリが確実な情報を報告しない例でもあります。WHEREAge<>22
SQL の 3 値論理が SQLデータ定義言語(DDL)と交差する主な場所は、チェック制約の形式です。列に設定されたチェック制約は、DMLWHERE句のルールとは少し異なる一連のルールに従って動作します。DMLWHERE句は行に対して True と評価されなければなりませんが、チェック制約は False と評価されてはなりません。(論理的な観点からは、指定された値は True と Unknown です。) これは、チェック制約は、チェックの結果が True または Unknown のいずれかであれば成功することを意味します。次のチェック制約付きの例のテーブルでは、列iに整数値を挿入することは禁止されますが、Null の場合はチェックの結果が常に Unknown と評価されるため、Null の挿入は許可されます。[ 19 ]
CREATE TABLE t ( i INTEGER , CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) );WHERE句に対する指定値の変更により、論理的な観点から見ると、排中律はCHECK制約に対してトートロジーとなり、常に成功します。さらに、Null値を存在するが未知の値として解釈すると仮定すると、上記のような一部の異常なCHECKでは、Null値を非Null値に置き換えることができない値を挿入できてしまいます。CHECK (p OR NOT p)
列にNULL値を拒否する制約を設定するには、NOT NULL以下の例に示すように制約を適用します。この制約は、述語付きのチェック制約NOT NULLと意味的に同等です。IS NOT NULL
テーブルtを作成します( iは整数型で、NULLは不可)。デフォルトでは、外部キーに対するチェック制約は、そのようなキーのいずれかのフィールドが Null の場合に成功します。たとえば、テーブル
CREATE TABLE Books ( title VARCHAR ( 100 ), author_last VARCHAR ( 20 ), author_first VARCHAR ( 20 ), FOREIGN KEY ( author_last , author_first ) REFERENCES Authors ( last_name , first_name ));NULLAuthors テーブルの定義方法や内容に関係なく、author_last または author_first が である行の挿入を許可します。より正確には、これらのフィールドのいずれかに null があると、Authors テーブルに存在しない値であっても、もう一方のフィールドに任意の値を挿入できます。たとえば、Authors に のみが含まれている場合('Doe', 'John')、 は('Smith', NULL)外部キー制約を満たします。SQL -92 では、このような場合に一致を絞り込むための 2 つのオプションが追加されました。宣言MATCH PARTIALの後に が追加されるとREFERENCES、null 以外の値はすべて外部キーに一致する必要があります。たとえば、 は('Doe', NULL)一致しますが、 は('Smith', NULL)一致しません。最後に、MATCH FULLが追加されると('Doe', NULL)、 も制約に一致しませんが、(NULL, NULL)は一致します。
NULL結果では、NULLマーカーはデータの代わりに単語で表されます。結果はMicrosoft SQL Serverから取得したもので、SQL Server Management Studioで表示されます。SQLの外部結合（左外部結合、右外部結合、完全外部結合を含む）では、関連テーブルの欠落値に対してプレースホルダーとして自動的にNULLが生成されます。たとえば、左外部結合の場合、演算子の右側に表示されるテーブルに存在しない行の代わりにNULLが生成されますLEFT OUTER JOIN。次の簡単な例では、2つのテーブルを使用して、左外部結合におけるNULLプレースホルダーの生成方法を示します。
最初のテーブル（Employee）には従業員ID番号と名前が、2番目のテーブル（PhoneNumber）には関連する従業員ID番号と電話番号が含まれています（下図参照）。
以下のサンプルSQLクエリは、これら2つのテーブルに対して左外部結合を実行します。
SELECT e.ID , e.LastName , e.FirstName , pn.Number FROM Employee e LEFT OUTER JOIN PhoneNumber pn ON e.ID = pn.ID ;このクエリによって生成された結果セットは、以下に示すように、SQL が右側の ( PhoneNumber ) テーブルに存在しない値のプレースホルダーとして Null を使用する方法を示しています。
SQL は、サーバー側でのデータ集計計算を簡素化するために集計関数COUNT(*)を定義します。関数を除き、すべての集計関数は Null 除去ステップを実行するため、Null は計算の最終結果に含まれません。[ 20 ]
Null を削除することは、Null をゼロに置き換えることと同等ではないことに注意してください。たとえば、次の表では、AVG(i)( の値の平均i) は とは異なる結果になりますAVG(j)。
はAVG(i)200 (150、200、250 の平均) であり、AVG(j)は 150 (150、200、250、0 の平均) です。このよく知られた副作用として、SQL では は とAVG(z)等価ではなく、SUM(z)/COUNT(*)と等価になりますSUM(z)/COUNT(z)。[ 4 ]
集計関数の出力はNullになる場合もあります。以下に例を示します。
SELECT COUNT ( * ) , MIN ( e.Wage ) , MAX ( e.Wage ) FROM Employee e WHERE e.LastName LIKE ' % Jones % ' ;このクエリは常に正確に 1 行を出力し、姓に「Jones」が含まれる従業員の数をカウントし、それらの従業員について見つかった最低賃金と最高賃金を示します。しかし、指定された条件に一致する従業員がいない場合はどうなるでしょうか。空の集合の最小値または最大値を計算することは不可能なので、結果は NULL となり、答えがないことを示します。これは不明な値ではなく、値が存在しないことを示す NULL です。結果は次のようになります。
SQL:2003ではすべての Null マーカーが互いに等しくないと定義されているため、特定の操作を実行する際に Null をグループ化するには特別な定義が必要でした。SQL では「互いに等しい 2 つの値、または任意の 2 つの Null」を「区別しない」と定義しています。 [ 21 ]この区別しないという定義により、句 (またはグループ化を実行する別の SQL 言語機能) を使用すると、SQL は Null をグループ化してソートすることができますGROUP BY。
NULL値の扱いにおいて「not distinct」定義を使用するその他のSQL操作、句、キーワードには、以下のものがあります。
PARTITION BYランキングおよびウィンドウ関数の条項などROW_NUMBERUNION、、INTERSECTおよび演算子はEXCEPT、行の比較/削除の目的でNULLを同じものとして扱います。DISTINCTで使用されるキーワードSELECTNULL は互いに等しくない (結果が不明である) という原則は、UNIONNULL を互いに識別する演算子の SQL 仕様では事実上破られています。[ 1 ]その結果、SQL の集合演算 (和集合や差集合など) では、NULL との明示的な比較を含む演算 (上記で説明した句の演算など) とは異なり、確実な情報を表さない結果が生成される場合がありますWHERE。Codd の 1979 年の提案 (SQL92 で採用) では、集合演算における重複の削除は「検索操作の評価における等価性テストよりも低いレベルで行われる」と主張することで、この意味論的な矛盾を合理化しています。[ 11 ]
SQL標準では、NULL値のデフォルトのソート順は明示的に定義されていません。代わりに、準拠システムでは、リストの句NULLS FIRSTまたは句を使用することで、NULL値をすべてのデータ値の前または後にソートできます。ただし、すべてのDBMSベンダーがこの機能を実装しているわけではありません。この機能を実装していないベンダーは、DBMSでのNULL値のソートに関して異なる処理を指定する場合があります。[ 19 ]NULLS LASTORDER BY
SQL製品の中には、NULLを含むキーをインデックス化しないものがあります。たとえば、PostgreSQLバージョン8.3より前のバージョンではインデックス化されておらず、 Bツリーインデックスのドキュメントには[ 22 ]と記載されています。
Bツリーは、何らかの順序でソート可能なデータに対する等価クエリと範囲クエリを処理できます。特に、PostgreSQLクエリプランナーは、インデックス付き列が以下の演算子のいずれかを使用した比較に関係する場合、Bツリーインデックスの使用を検討します。< ≤ = ≥ >
BETWEENやINなど、これらの演算子の組み合わせに相当する構文も、Bツリーのインデックス検索で実装できます。（ただし、IS NULLは=と等価ではなく、インデックス化できないことに注意してください。）
インデックスが一意性を強制する場合、NULL はインデックスから除外され、NULL 間では一意性は強制されません。PostgreSQL のドキュメントから引用すると次のようになります。[ 23 ]
インデックスが一意であると宣言されている場合、インデックス値が等しい複数のテーブル行は許可されません。NULL値は等しいとはみなされません。複数列の一意インデックスは、2つの行ですべてのインデックス列が等しい場合にのみ拒否します。
これは、SQL:2003で定義されているスカラーNULL比較の動作と一致しています。
NULL をインデックス化するもう 1 つの方法は、SQL:2003 で定義された動作に従って、それらを区別しないものとして扱うことです。たとえば、 Microsoft SQL Server のドキュメントには次のように記載されています。[ 24 ]
インデックス作成においては、NULL値は等しい値として扱われます。そのため、キーが複数の行でNULL値である場合、一意インデックスまたは一意制約を作成することはできません。一意インデックスまたは一意制約の列を選択する際は、NOT NULLとして定義されている列を選択してください。
これらのインデックス作成戦略はどちらも、SQL:2003で定義されているNULL値の動作と一致しています。SQL:2003標準ではインデックス作成方法が明示的に定義されていないため、NULL値のインデックス作成戦略の設計と実装は完全にベンダーに委ねられています。
SQL は、NULL を明示的に処理するための 2 つの関数を定義しています。NULLIFと ですCOALESCE。どちらの関数も検索CASE式の略語です。[ 25 ]
このNULLIF関数は2つの引数を受け取ります。最初の引数と2番目の引数が等しい場合は、NULLIFNullを返します。そうでない場合は、最初の引数の値が返されます。
NULLIF ( value1 , value2 )したがって、NULLIFは次の式の略語ですCASE。
CASE WHEN value1 = value2 THEN NULL ELSE value1 ENDこのCOALESCE関数はパラメータのリストを受け取り、そのリストから最初の非Null値を返します。
COALESCE ( value1 , value2 , value3 , ...)COALESCEこれは、次の SQLCASE式の省略形として定義されます。
CASE WHEN value1 IS NOT NULL THEN value1 WHEN value2 IS NOT NULL THEN value2 WHEN value3 IS NOT NULL THEN value3 ... END一部のSQL DBMSは、に類似したベンダー固有の関数を実装していますCOALESCE。一部のシステム（Transact-SQLなど）はISNULL、関数、または機能的に類似した他の類似関数を実装していますCOALESCE。（ Transact-SQLの関数については、Is関数をIS参照してください。）
OracleのNVL関数は2つのパラメータを受け取ります。NULL以外の最初のパラメータを返し、すべてのパラメータがNULLの場合はNULLを返します。
式は、次のようにしてCOALESCE同等の式に変換できますNVL。
COALESCE ( val1 , ... , val { n } )次のように変わります:
NVL ( val1 , NVL ( val2 , NVL ( val3 , … , NVL ( val { n - 1 } , val { n } ) … )))この関数の使用例としては、式の中でNULLを値に置き換えることが挙げられます。例えばNVL(SALARY, 0)、「SALARYNULLの場合は、値0に置き換える」といった場合です。
ただし、注目すべき例外が1つあります。ほとんどの実装では、COALESCE最初のNULL以外のパラメータに到達するまでパラメータを評価しますが、はすべてのパラメータを評価します。これはいくつかの理由で重要です。最初のNULL以外のパラメータの後のNVLパラメータは関数である可能性があり、計算コストが高かったり、無効であったり、予期しない副作用を引き起こしたりする可能性があります。
SQL ではリテラルNULLは型指定されていません。つまり、整数、文字、またはその他の特定のデータ型として指定されていません。[ 26 ]このため、Null を特定のデータ型に明示的に変換することが必須（または望ましい）になる場合があります。たとえば、 RDBMS がオーバーロードされた関数をサポートしている場合、SQL は、Null が渡されるものを含め、すべてのパラメーターのデータ型を知らないと、正しい関数に自動的に解決できない可能性があります。
SQL-92で導入された機能NULLを使用すると、リテラルから特定の型のNullへの変換が可能です。例：CAST
CAST ( NULL AS INTEGER )INTEGER型の値が存在しないことを表します。
Unknown の実際の型（NULL 自体と異なるか否か）は、SQL の実装によって異なります。例えば、次のようになります。
SELECT 'ok' WHERE ( NULL <> 1 ) IS NULL ;NULL ブール値を Unknown と統一する一部の環境 ( SQLiteやPostgreSQLなど) では正常に解析および実行されますが、他の環境 ( SQL Server Compactなど) では解析に失敗します。MySQLはこの点においてPostgreSQLと同様の動作をします(ただし、 MySQL ではTRUE と FALSE を通常の整数 1 と 0 と区別しないという小さな例外があります)。PostgreSQL にはさらにIS UNKNOWN述語が実装されており、3 つの値の論理結果が Unknown かどうかをテストするために使用できますが、これは単なる構文糖衣です。
ISO SQL:1999規格では、SQLにBOOLEANデータ型が導入されましたが、これは依然としてオプションの非コア機能であり、コードはT031です。[ 27 ]
制約によって制限されている場合NOT NULL、SQL の BOOLEAN は他の言語のブール型と同様に機能します。ただし、制約がない場合、BOOLEAN データ型は、その名前にもかかわらず、TRUE、FALSE、および UNKNOWN の真偽値を保持できます。これらはすべて、標準に従ってブール リテラルとして定義されています。標準では、NULL と UNKNOWN は「まったく同じ意味を表すために互換的に使用できる」とも規定されています。[ 28 ] [ 29 ]
ブール型は、特にUNKNOWNリテラルの必須動作のために批判の対象となってきた。UNKNOWNリテラルはNULLと同一視されるため、決してそれ自身と等しくなることはない。[ 30 ]
前述のように、PostgreSQLのSQL実装では、UNKNOWN BOOLEAN を含むすべての UNKNOWN 結果を表すために Null が使用されます。PostgreSQL は UNKNOWN リテラルを実装していません (ただし、直交機能である IS UNKNOWN 演算子は実装しています)。2012 年現在、他のほとんどの主要ベンダーは、Boolean 型 (T031 で定義されているもの) をサポートしていません。 [ 31 ]ただし、Oracle のPL/SQLの手続き部分はBOOLEAN 変数をサポートしており、これらにも NULL を割り当てることができ、その値は UNKNOWN と同じとみなされます。[ 32 ]
Null の動作に関する誤解は、ISO 標準 SQL ステートメントと実際のデータベース管理システムでサポートされている特定の SQL 方言の両方において、SQL コードで多くのエラーを引き起こす原因となっています。これらの間違いは通常、Null と 0 (ゼロ) または空文字列 (長さがゼロの文字列値、SQL では と表記'') との混同から生じます。しかし、SQL 標準では Null は空文字列と数値 0 とは異なるものとして定義されています0。Null は値が存在しないことを示しますが、空文字列と数値 0 はどちらも実際の値を表します。
=よくあるエラーは、キーワードと組み合わせてイコール演算子を使用してNULLNULL値を含む行を検索しようとすることです。SQL標準によれば、これは無効な構文であり、エラーメッセージまたは例外が発生します。しかし、ほとんどの実装ではこの構文を受け入れ、このような式を と評価します。その結果、NULL値を含む行が存在するかどうかにかかわらず、行は見つかりません。NULL値を含む行を取得する推奨方法は、の代わりにUNKNOWN述語を使用することです。IS NULL= NULL
SELECT * FROM sometable WHERE num = NULL ; -- "WHERE num IS NULL" とすべきです関連する、しかしより微妙な例として、WHERE句または条件文で列の値を定数と比較する場合があります。欠損値が定数より「小さい」または「等しくない」と誤解されがちですが、実際には、そのような式は「不明」を返します。以下に例を示します。
SELECT * FROM sometable WHERE num <> 1 ; -- 多くのユーザーの期待とは異なり、num が NULL の行は返されません。これらの混乱は、 SQLの論理では同一性の法則NULLが制限されているために発生します。リテラルまたは真理値を使用して等価比較を扱う場合、SQLは常に式の結果としてUNKNOWN返します。これは部分的な等価関係であり、SQLを非反射的論理の例にしています。[ 33 ]UNKNOWN
同様に、ヌル値は空文字列と混同されることがよくあります。LENGTH文字列の文字数を返す関数を考えてみましょう。この関数にヌル値が渡されると、関数はヌル値を返します。ユーザーが3値論理に精通していない場合、これは予期しない結果につながる可能性があります。以下に例を示します。
SELECT * FROM sometable WHERE LENGTH ( string ) < 20 ; -- string が NULL の行は返されません。さらに複雑なことに、一部のデータベースインターフェースプログラム（あるいはOracleのようなデータベース実装）では、NULLが空文字列として報告され、空文字列が誤ってNULLとして格納される可能性がある。
ISO SQL の Null の実装は、批判、議論、変更要求の対象となっています。『データベース管理のためのリレーショナル モデル: バージョン 2』の中で、Codd は SQL の Null の実装に欠陥があり、2 つの異なる Null 型マーカーに置き換えるべきだと提案しました。彼が提案したマーカーは、「Missing but Applicable」と「Missing but Inapplicable」を表し、それぞれA 値とI 値として知られています。Codd の提案が受け入れられた場合、SQL で 4 値ロジックを実装する必要が生じます。[ 5 ]他にも、Codd の提案に Null 型マーカーを追加して、データ値が「Missing」になる理由をさらに多く示すことで、SQL のロジック システムの複雑さを増すという提案がありました。また、さまざまな時期に、SQL で複数のユーザー定義 Null マーカーを実装するという提案もなされました。複数の Null マーカーをサポートするために必要な Null 処理とロジック システムの複雑さのため、これらの提案はいずれも広く受け入れられていません。
『第三マニフェスト』の著者であるクリス・デイトとヒュー・ダーウェンは、 SQL Null の実装は本質的に欠陥があり、完全に排除すべきだと提唱している[ 34 ]。彼らは、SQL Null 処理の実装における矛盾や欠陥（特に集計関数）を指摘し、Null の概念全体が欠陥があり、リレーショナル モデルから削除されるべきであると主張している[ 35 ] 。一方、著者ファビアン・パスカルのような他の人々は、「関数の計算で欠損値をどのように扱うべきかは、リレーショナル モデルによって規定されるものではない」という信念を表明している。
ヌルに関するもう 1 つの矛盾点は、ヌルが関係データベースの閉鎖世界仮定モデルに違反し、開放世界仮定を導入していることである。[ 36 ]データベースに関する閉鎖世界仮定は、「データベースによって明示的または暗黙的に述べられていることはすべて真であり、それ以外はすべて偽である」と述べている。[ 37 ]この見解は、データベースに格納されている世界の知識が完全であると仮定している。しかし、ヌルは開放世界仮定の下で動作し、データベースに格納されている項目の一部は未知であるとみなされ、データベースに格納されている世界の知識が不完全になる。
{{cite web}}:欠落または空欄|url=(ヘルプ)