MySQLの3値ロジックとNULLの詳細な説明

NULLとは何か

NULL は、特定の型の値ではなく、欠損値または不明なデータを表すために使用されます。データ テーブル内の NULL 値は、値が配置されているフィールドが空であることを意味します。NULL 値を持つフィールドには値がありません。NULL 値は 0 または空の文字列とは異なることを理解することが特に重要です。

2種類のNULL

SQL には NULL の種類が 1 つしかないため、このステートメントは奇妙に思えるかもしれません。ただし、NULL について議論する場合、通常は「不明」と「該当なし、不該当」の 2 つのタイプとして考えます。

「サングラスをかけている人の目の色がわからない」という例を考えてみましょう。この人の目には確かに色がありますが、メガネを外さなければ、他の人は彼の目の色を知ることができません。これを未知と呼びます。そして、「冷蔵庫の目の色が何色か分からない」は「該当なし」です。冷蔵庫には目がないので、「目の色」という特性は冷蔵庫には当てはまりません。 「冷蔵庫の目の色」のような発言は、「円の体積」や「男性が産む子供の数」のような発言と同じくらい意味がありません。普段、私たちは「わかりません」と言うことに慣れていますが、「わかりません」にもいろいろな種類があります。この場合の「該当なし」の NULL は、意味的には「不確定」というより「無意味」に近いです。まとめると、「不明」とは「今は分からないが、一定の条件を満たせば分かる」という意味で、「該当なし」とは「どんなに努力しても分からない」という意味です。

この分類を最初に提唱したのは、リレーショナル モデルの発明者である EF Codd です。以下は彼の「失われた情報」の分類である。

なぜ「＝ NULL」ではなく「IS NULL」と書く必要があるのでしょうか?

多くの人が、特に SQL を学んだばかりの人たちは、このような混乱を抱えていると思います。具体的なケースを見てみましょう。次の表とデータがあると仮定します。

t_sample_nullが存在する場合はテーブルを削除します。
テーブルt_sample_nullを作成します（
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
    名前 VARCHAR(50) NOT NULL COMMENT '名前',
    備考 VARCHAR(500) COMMENT '備考',
    主キー(ID)
) COMMENT 'NULL の例';

t_sample_null(名前、コメント)に挿入する
VALUES('zhangsan', '張三'),('李四', NULL);

NULL コメント付きのレコードをクエリします (NULL は正しい用語ではありませんが、日常生活ではよく使われています。詳細については以下を参照してください)。クエリを実行するにはどうすればよいでしょうか。多くの初心者は次のような SQL を記述します。

-- SQL はエラーを報告しませんが、結果が見つかりません SELECT * FROM t_sample_null WHERE remark = NULL; 

実行時にエラーは発生しませんが、必要な結果が得られません。なぜでしょうか?この質問は一旦脇に置いて、次の質問に移りましょう

3値論理

この三値論理は三項演算ではなく、3つの論理値を指します。疑問に思う人もいるかもしれません。論理値は真と偽だけではないのですか？3つ目はどこから来るのですか？これを言うとき、私たちがいる環境に注意を払う必要があります。主流のプログラミング言語（C、JAVA、Python、JSなど）では、確かに論理値は2つしかありませんが、SQLには3番目の論理値であるunknownがあります。これは、私たちが普段言っていることと似ています。正しい、間違っている、わかりません。

論理値 unknown と NULL の一種である UNKNOWN は異なるものです。前者は明確なブール論理値ですが、後者は値でも変数でもありません。簡単に区別するために、前者は小文字の unknown で表され、後者は大文字の UNKNOWN で表されます。両者の違いを理解しやすくするために、x=x のような簡単な方程式を見てみましょう。 x が未知の論理値である場合、x=x は真とみなされ、x が UNKNOWN である場合、不明とみなされます。

-- これは明確な論理値の比較です。不明 = 不明 → 真

-- これはNULL = NULLと同等です
不明 = 不明 → 不明

3値論理の論理値表

ない

そして

または

図中の青い部分は、2値論理には存在しない、3値論理特有の演算です。他のすべての SQL 述語は、これら 3 つの論理演算で構成できます。この意味で、これらの論理テーブルは SQL のマトリックスであると言えます。

NOTの場合は論理値表が比較的単純なので覚えやすいのですが、ANDやORの場合は組み合わせられる論理値がたくさんあるので、すべて覚えるのは非常に困難です。覚えやすくするために、これら 3 つの論理値の間には次の優先順位があることに注意してください。

AND の場合: false > unknown > true

または状況: true > 不明 > false

優先順位の高い論理値が計算結果を決定します。たとえば、true AND unknown の場合、unknown の優先度が高いため、結果は unknown になります。 true または unknown の場合、true の方が優先度が高いため、結果は true になります。この順序を覚えておくと、3 値論理演算が簡単に実行できるようになります。 AND 演算に不明な要素が含まれる場合、結果は必ず true にならないことを覚えておくことが重要です (逆に、AND 演算の結果が true の場合、演算に関係する両方の要素が true である必要があります)。

-- a = 2、b = 5、c = NULL と仮定すると、次の式の論理値は次のようになります: a < b AND b > c → 不明
a > b または b < c → 不明
a < b または b < c → 真
NOT (b <> c) → 不明

「= NULL」ではなく「IS NULL」

質問に戻りましょう。なぜ「＝ NULL」ではなく「IS NULL」と書く必要があるのでしょうか?

NULL の比較述語の結果は常に不明です。クエリ結果には、WHERE 句の判定結果が true である行のみが含まれ、判定結果が false または不明である行は含まれません。等号だけでなく、NULL の他の比較述語でも同じ結果になります。したがって、remark が NULL であるかどうかに関係なく、比較結果は不明であり、結果は返されません。次の式は未知と判断されます

-- 次の式は未知数と判断されます
= NULL
> ヌル
< NULL
<> NULL
NULL = NULL

では、なぜ NULL の比較述語は true と評価されないのでしょうか?これは、NULL が値でも変数でもないためです。 NULL は単に「値なし」を意味するトークンであり、比較述語は値にのみ適用されます。したがって、値ではない NULL に対して比較述語を使用することは意味がありません。 「列の値は NULL です」や「NULL 値」などの記述は、それ自体が間違っています。 NULL は値ではないため、ドメイン内にはありません。一方、NULL を値と見なす場合は、逆に考えることができます。つまり、NULL はどのようなタイプの値でしょうか。リレーショナル データベース内の値は、文字や数値などの特定の型である必要があります。したがって、NULL が値である場合、それは何らかの型である必要があります。

NULL が簡単に値としてみなされる理由は 2 つあります。 1 つ目は、高級プログラミング言語では NULL が定数として定義され (多くの言語では整数 0 として定義されています)、混乱を招くことです。ただし、SQL の NULL は他のプログラミング言語の NULL とはまったく異なります。 2 番目の理由は、IS NULL のような述語は 2 つの単語で構成されているため、IS を述語として、NULL を値として扱うのが簡単だからです。特にSQLにはIS TRUEやIS FALSEといった述語があるので、類推してそう考えるのは不合理ではありません。しかし、標準 SQL に関する書籍で指摘されているように、IS NULL は述語として考える必要があります。したがって、IS_NULL と記述する方が適切かもしれません。

ジェントルトラップ

述語をNULLと比較する

排中律は成り立ちません。排中律は、同じ思考プロセスにおいて、2つの矛盾する考えが同時に偽であることはできず、1つは真、つまり「AかAでないかのどちらか」でなければならないとしています。

学生テーブルt_studentがあるとします。

t_student が存在する場合はテーブルを削除します。
テーブル t_student を作成します (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
    名前 VARCHAR(50) NOT NULL COMMENT '名前',
    年齢 INT(3) COMMENT '年齢',
    注釈 VARCHAR(500) NOT NULL DEFAULT '' COMMENT '注釈',
    主キー(ID)
) COMMENT '学生情報';

t_student(名前、年齢)に挿入
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18);

t_student から * を選択します。

テーブル内のデータ yzb の年齢は NULL です。つまり、yzb の年齢は不明です。現実世界では、yzb は 20 歳であるか、20 歳ではないかのどちらかであり、そのどちらかが当てはまるはずです。これは間違いなく正しい命題です。ではSQLの世界でも排中律は適用されるのでしょうか？SQLを見てみましょう

t_studentから*を選択
ここで、年齢 = 20 または年齢 <> 20;

一見すると、これがクエリ テーブル内のすべてのレコードではないでしょうか?実際の結果を見てみましょう

yzb は見つけられませんでした、なぜですか?分析してみましょう。yzb の age は NULL です。このレコードの判断手順は次のとおりです。

-- 1. ジョンの年齢は NULL です (不明な NULL!)
選択*
t_studentより
年齢 = NULLの場合
または年齢 <> NULL;

-- 2. NULLに比較述語を使用した後、結果は不明です
選択*
t_studentより
場所不明
または不明。

-- 3.不明OR不明の結果は不明です（3値論理の論理値表を参照）
選択*
t_studentより
場所不明。

SQL ステートメントのクエリ結果には、判定結果が true である行のみが含まれます。 yzbを結果に表示するには、次の「3番目の条件」を追加する必要があります。

-- 3 つの条件を追加します: 年齢が 20 歳、20 歳以外、または年齢が不明 SELECT * FROM t_student
年齢 = 20 
    または年齢 <> 20
    または、年齢が NULL です。

CASE 式と NULL

単純なCASE式は次のようになります。

ケース col_1
    = 1 の場合 'o'
    NULLの場合は「x」
終わり

この CASE 式は × を返すことはありません。これは、2 番目の WHEN 句が col_1 = NULL の省略形であるためです。ご存知のとおり、この式の論理値は常に不明であり、CASE 式の判断方法は WHERE 句と同じで、論理値が true の条件のみを認識します。正しい書き方は、次のような検索 CASE 式を使用することです。

col_1 = 1 の場合 'o'
    col_1がNULLの場合は'x'
終わり

NOT INとNOT EXISTSは同等ではありません

SQL ステートメントのパフォーマンスを最適化するときによく使用されるトリックは、IN を EXISTS に書き換えることです。これは同等の書き換えであり、問​​題はありません。ただし、NOT IN を NOT EXISTS に書き換えると、結果が同じにならない可能性があります。

例を見てみましょう。 t_student_A と t_student_B という 2 つのテーブルがあり、それぞれクラス A とクラス B の生徒を表しています。

t_student_A が存在する場合はテーブルを削除します。
テーブル t_student_A を作成します (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
    名前 VARCHAR(50) NOT NULL COMMENT '名前',
    年齢 INT(3) COMMENT '年齢',
    都市 VARCHAR(50) NOT NULL COMMENT '都市',
    注釈 VARCHAR(500) NOT NULL DEFAULT '' COMMENT '注釈',
    主キー(ID)
) COMMENT '学生情報';

t_student_A(名前、年齢、都市)に挿入
価値
('zhangsan'、25、'深圳市')、('wangwu'、60、'広州市')、
('ブルース', 32, '北京'),('yzb', NULL, '深セン'),
（「ボス」、43歳、「深セン市」）

t_student_B が存在する場合はテーブルを削除します。
テーブル t_student_B を作成します (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
    名前 VARCHAR(50) NOT NULL COMMENT '名前',
    年齢 INT(3) COMMENT '年齢',
    都市 VARCHAR(50) NOT NULL COMMENT '都市',
    注釈 VARCHAR(500) NOT NULL DEFAULT '' COMMENT '注釈',
    主キー(ID)
) COMMENT '学生情報';

t_student_Bに挿入(名前、年齢、都市)
価値
(「馬化騰」、45歳、「深圳市」)、(「馬三」、25歳、「深圳市」)、
(ジャック・マー、43歳、杭州)、(ロビン・リー、41歳、深セン)、
（「若者」、25歳、「深セン」）

 * t_student_B から;

要件: 深圳に住むクラス A の生徒とは年齢が異なるクラス B の生徒を照会します。つまり、クエリ: Ma Huateng と Li Yanhong。この SQL はどのように記述すればよいでしょうか。

-- 深センに住むクラス A の生徒とは年齢が異なるクラス B の生徒を見つけますか?
t_student_Bから*を選択
年齢が含まれない場所（
    t_student_Aから年齢を選択 
    ここで、都市 = '深セン'
);

実行結果を見てみましょう

結果が空で、データが見つからないことがわかりました。なぜでしょうか?ここで、NULL が再び問題を引き起こし始めます。何が起こるかを段階的に見てみましょう。

-- 1. サブクエリを実行して年齢リストを取得します SELECT * FROM t_student
年齢が(43, NULL, 25)ではない場合;

-- 2. NOT IN を NOT と IN を使って同等に書き直す
t_studentから*を選択
年齢が (43, NULL, 25) に該当しません。

-- 3. OR等価性を使用して述語INを書き換える
t_studentから*を選択
WHERE NOT ( (年齢 = 43) OR (年齢 = NULL) OR (年齢 = 25) );

-- 4. ド・モルガンの法則を使ってSELECT * FROM t_studentを書き直す
WHERE NOT (年齢 = 43) AND NOT (年齢 = NULL) AND NOT (年齢 = 25);

-- 5. <> を使用して NOT と = を書き換えます。
t_studentから*を選択
WHERE (年齢 <> 43) AND (年齢 <> NULL) AND (年齢 <> 25);

-- 6. NULLに<>を使用した後、結果は不明です
t_studentから*を選択
WHERE (年齢 <> 43) AND 不明 AND (年齢 <> 25);

-- 7. AND演算に不明な値が含まれている場合、結果は真ではありません（3値論理の論理値表を参照）
t_studentから*を選択
誤りまたは不明な場合;

一連の変換の後、WHERE 句で true と判断されるレコードがないことがわかります。つまり、NOT IN サブクエリで使用されるテーブルの選択された列に NULL がある場合、SQL ステートメント全体のクエリ結果は常に空になります。これは恐ろしい現象です！

正しい結果を得るには、EXISTS述語を使用する必要があります。

-- 正しいSQL文: SELECT * FROM t_student_B BでMa HuatengとLi Yanhongが照会されます
存在しない場所（ 
    t_student_Aから*を選択
    ここで、B.age = A.age
    AND A.city = '深セン' 
);

実行結果は次のとおりです

同様に、この SQL ステートメントが age が NULL の行をどのように処理するかを見てみましょう。

-- 1. サブクエリでNULLを使用して比較演算を実行し、A.ageはNULLです
t_student_Bから*を選択
存在しない場所（
    t_student_Aから*を選択
    B.age = NULLの場合
    AND A.city = '深セン' 
);

-- 2. NULLに「=」を使用した後、結果は不明です
t_student_Bから*を選択
存在しない場所（
    t_student_Aから*を選択
    場所不明
    AND A.city = '深セン' 
);

-- 3. AND演算に不明な値が含まれている場合、結果は真になりません
t_student_Bから*を選択
存在しない場所（
    t_student_Aから*を選択
    誤りまたは不明な場合
);

-- 4. サブクエリは結果を返さないので、逆にNOT EXISTSは真である。
t_student_Bから*を選択
真の場合;

つまり、yzb さんは「他の誰とも年齢が違う人」として扱われていたのです。 EXISTS は true または false のみを返し、不明な値は返しません。そのため、IN と EXISTS は互換的に使用できるが、NOT IN と NOT EXISTS は互換的に使用できないという混乱が生じる現象が発生します。

他にも、修飾述語と NULL、修飾述語と極値関数は同等ではない、集計関数と NULL など、いくつかの落とし穴があります。

要約する

1. NULL は欠損値や不明なデータを表すために使用されます。特定の型の値ではないため、述語で使用することはできません。

2. NULL に述語を使用した場合の結果は不明です。論理演算に不明が含まれる場合、SQL は期待どおりに実行されません。3. IS NULL は述語であり、IS が述語、NULL が値ではありません。IS TRUE および IS FALSE と同様です。4. NULL によって発生するさまざまな問題を解決するには、テーブルに NOT NULL 制約を追加して NULL を除外するのが最善の方法です。

以上がMySQLの3値ロジックとNULLの詳しい説明です。MySQLの3値ロジックとNULLについてさらに詳しく知りたい方は、123WORDPRESS.COMの他の関連記事もぜひご覧ください！