MySQLクエリ条件のnot inとinの違いと理由

MySQLクエリ条件のnot inとinの違いと理由

まずSQLを書く

SELECT DISTINCT from_id
タラから
cod.from_id が (37, 56, 57) に含まれない場所

今日 SQL を書いていたところ、クエリ結果が不完全で、NULL 値が欠落しており、さらに存在しない場合は null が除外されていることに気付きました。

inを使用する場合、nullは含まれません

MySQL は適切に設計されていないように感じます。

なぜなら、in と not in は互いに補完し合うべきだと私は常に思っていたからです。検索全体は次のようになるはずです。

SELECT DISTINCT from_id
タラから
cod.from_id が (37, 56, 57) ではない、または cod.from_id が (37, 56, 57) である

結果は予想通りで、nullが欠落しています

後でオンラインで調べたところ、合理的な説明が見つかりました。

nullはどの値と比較しても偽である

たとえば、from_id が (37, 56, 57,28,null) の場合、28 と比較すると not in (37, 56, 57) は true なので、結果セットには 28 が表示されます。

null を条件 not in (37, 56, 57) と比較すると、結果は false となるため、結果セットには表示されません。

補足:MySQL条件クエリINとNOT INの両側でNULL値を処理する方法

トピック

テーブル ツリーの場合、id はツリー ノードの番号であり、p_id はその親ノードの ID です。

+----+------+

| id | p_id |

+----+------+

| 1 | NULL |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

+----+------+

ツリー内の各ノードは、次の 3 つのタイプのいずれかになります。

リーフ: このノードに子ノードがない場合。

ルート: このノードがツリー全体のルートである場合、つまり親ノードがない場合。

内部ノード: ノードがリーフ ノードでもルート ノードでもない場合。

すべてのノードの数とタイプを出力するクエリ ステートメントを記述し、結果をノード番号で並べ替えます。上記の例の結果は次のようになります。

+----+------+

| id | タイプ |

+----+------+

| 1 | ルート |

| 2 | 内側|

| 3 | 葉 |

| 4 | 葉 |

| 5 | 葉 |

+----+------+

説明する

ノード '1' は親が NULL であり、子ノード '2' と '3' を持つため、ルート ノードです。

ノード「2」は親ノード「1」を持ち、子ノード「4」と「5」も持っているため、内部ノードです。

ノード「3」、「4」、および「5」は親ノードを持ち、子ノードがないため、リーフノードです。

この例のツリーは次のようになります。

 1

 / \\

 23

 / \\

 4 5

まずテーブルを作成する

1. テーブルを作成する

テーブルツリーの作成(
id INT 、
p_id 整数 
)

やり方は次のとおりです:

SELECT id,(
場合 
 tree.p_id が NULL の場合、'Root'
 WHEN tree.id NOT IN ( -- id が親ノードの p_id 列にない場合は、論理的に正しいリーフノードと見なされます。
 p_idを選択
 木から
 GROUP BY p_id
 )そして「葉」
 ELSE「内側」
終わり
)タイプ
木から

親ノードの p_id 列に id がない場合、リーフ ノードと見なされると思います。論理的にはまったく問題はありません。ただし、物事はそれほど単純ではありません。クエリ結果は次のとおりです。id=3 から始めて、必要な結果が見つかりませんでした。すごいですね!

そしてもう一晩経って、ついに問題を解決しました。まずは正しいアプローチを紹介します。

SELECT id,(
場合 
 tree.p_id が NULL の場合、'Root'
 tree.id が ( に含まれない ) の場合
 p_idを選択
 木から
 WHERE p_id IS NOT NULL -- SQL文を追加しました
 GROUP BY p_id
 )そして「葉」
 ELSE「内側」
終わり
)タイプ
木から

なぜこのようなことが起こるのでしょうか?

周知された

MySQL の IN 演算子は、式の値が指定されたリスト内にあるかどうかを判断するために使用されます。リスト内にある場合、戻り値は 1 になり、それ以外の場合は戻り値は 0 になります。

NOT IN の機能は IN と正反対です。NOT IN は、指定されたリストに式の値が存在しないかどうかを判断するために使用されます。存在しない場合は戻り値は 1 になり、存在する場合は戻り値は 0 になります。

これが私たちが通常使用する方法であり、結果は私たちが望んでいる通りです。しかし、次のような特殊な状況に遭遇することがよくあります。

(1)inまたはnot inのどちらの側にもNULL値は存在しない

[例 1] SQL 文で IN 演算子と NOT IN 演算子を使用する:

mysql> SELECT 2 IN (1,3,5,'thks'),'thks' IN (1,3,5, 'thks');
+---------------------+---------------------------+
| 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5, 'thks') |
+---------------------+---------------------------+
| 0 | 1 |
+---------------------+---------------------------+
セットに 1 行、警告 2 件 (0.00 秒)

mysql> SELECT 2 NOT IN (1,3,5,'thks'),'thks' NOT IN (1,3,5, 'thks');
+-------------------------+-------------------------------+
| 2 は (1,3,5,'thks') に含まれません | 'thks' は (1,3,5, 'thks') に含まれません |
+-------------------------+-------------------------------+
| 1 | 0 |
+-------------------------+-------------------------------+
セットに 1 行、警告 2 件 (0.00 秒)

結果から、IN と NOT IN の戻り値がまったく逆であることがわかります。

しかし、私はNULL値の問題を無視しました

NULL値の取り扱い

IN 演算子のいずれかの側が NULL の場合、一致するものが見つからない場合、戻り値は NULL になります。一致するものが見つかった場合、戻り値は 1 になります。

(2)NULL値はinの両側にある

次の SQL ステートメントを参照してください。

mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
+------------------------+-------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+---------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
| NULL | 1 |
+------------------------+--------------------------+
セット内の 1 行 (0.00 秒)

(3)NOT INの片側がNULL

NOT IN は正反対です。NOT IN 演算子の 2 つの辺のうちの 1 つが NULL 値 NULL の場合、一致するものが見つからない場合は戻り値は NULL になり、一致するものが見つかった場合は戻り値は 0 になります。

次の SQL ステートメントを参照してください。

mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');
+----------------------------+----------------------------+
| NULL は (1,3,5,'thks') に含まれません | 10 は (1,0,NULL,'thks') に含まれません |
+----------------------------+----------------------------+
| NULL | NULL |
+----------------------------+----------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');
+----------------------------+------------------------------+
| NULL は (1,3,5,'thks') に含まれません | 10 は (1,10,NULL,'thks') に含まれません |
+----------------------------+------------------------------+
| NULL | 0 |
+----------------------------+------------------------------+
セット内の 1 行 (0.00 秒)

(3)NOT INの片側がNULLであることから、問題が分かります。

まず次のSQL文をクエリして、ゆっくりと問題を見つけてみましょう。

p_idを選択
木から
GROUP BY p_id

上記のクエリ結果にはNULL値が含まれています

したがって、次のSQL文はNOT INがNULLを返すため、何も見つかりません。

選択ID 
木から
id が ( に含まれない )
 p_idを選択
 木から
 GROUP BY p_id
 )

したがって、結果を照会する場合は、まず NULL 値を処理する必要があります。はい、バグは修正されました!

この問題を解く別の方法があります:

SELECT id,(
場合 
 tree.p_id が NULL の場合、'Root'
 tree.idがIN(
 p_idを選択
 木から
 GROUP BY p_id
 )そして「内側」
 ELSE「葉」
終わり
)タイプ
木から

なぜそれが正しいのでしょうか?それは皆さんに考えさせてください〜

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。間違いや不備な点がありましたら、遠慮なくご指摘ください。

以下もご興味があるかもしれません:
  • MySQLはクエリ条件としてJSONフィールドの内容に基づいてデータを取得します(JSON配列を含む)
  • Mysqlクエリ条件で文字列の末尾にスペースがあっても一致しない問題の詳細な説明
  • MySQLクエリ条件の一般的な使用法の詳細な説明
  • インデックスは MySQL クエリ条件で使用されますか?
  • MySQLクエリ条件におけるonとwhereの配置の違いの分析
  • MySQLはクエリ条件を最適化する方法を説明しています

<<:  カルーセル効果を作成するためのjs

>>:  XHTML チュートリアル、XHTML の基礎を簡単に紹介します

推薦する

CSS 動的高さ遷移アニメーション効果の実装

この質問は、Nuggets のメッセージから生まれました。友人が、次のコードの高さ遷移アニメーション...

MySQL でデータ復旧に binlog を使用する方法

序文最近、オンラインでデータが誤って操作されました。データベースが直接変更されたため、それを回復する...

MySQL 8.0.20 Window10無料インストール版設定とNavicat管理チュートリアルグラフィック詳細説明

1. MySQL 8.0.20をダウンロードして解凍するダウンロードリンク: https://dev...

VMware15/16 VMwareのロックを解除してMacOSをインストールする詳細な手順

VMware バージョン: VMware-workstation-full-16 VMware バー...

Alibaba Cloud CentOS7 サーバーの nginx 構成と FAQ の分析

序文:この記事は、jackyzm のブログ https://www.cnblogs.com/jack...

Dockerfile を使用したカスタムイメージの構築の実装

目次序文Dockerfile の紹介Dockerfileはイメージプロセスを構築するDockerfi...

Linux での MySQL 5.7.19 のインストールに関する問題の概要

初めて仮想マシンに MySQL をインストールしたとき、多くの問題が発生しました。ここでそれらを書き...

Vueがsweetalert2プロンプトコンポーネントを統合する際の問題についてお話ししましょう

目次1. プロジェクト統合1. CDNインポート方法: 2. 箱の梱包を確認する3. 迅速な箱詰め4...

Mysql一時テーブルの原理と作成方法の分析

この記事は主にMysql一時テーブルの原理と作成方法を紹介します。この記事のサンプルコードは非常に詳...

HTML ハイパーリンクの詳細な説明

ハイパーリンクハイパーリンクは、Web サイト上のすべてのページがハイパーリンクで接続され、ページ間...

Linux システムの busybox に mkfs.vfat コマンドを移植する

オーディオおよびビデオ ファイルを保存するためのディスク寿命を延ばすには、ディスクをフォーマットする...

Vue3+Vue-cli4 プロジェクトで Tencent スライダー検証コードを使用する方法

導入:従来の画像検証コードと比較して、スライダー検証コードには次の利点があります。サーバーは検証コー...

純粋な CSS で中空効果を実現するためのサンプルコード

私は最近、空洞化効果について研究しました。背景クリップ: テキスト背景はテキストの前景色にクリップさ...

親子コンポーネントの通信を解決するための3つのVueスロット

目次序文環境の準備カテゴリコンポーネントアプリのコンポーネント1. デフォルトスロット2. 名前付き...

Centos7 Zabbix3.4 メールアラーム設定(メール内容がxx.bin添付ファイルになる問題の解決)

目次1. 監視Linuxホストを追加する2. メールボックスを設定する1. 監視Linuxホストを追...