MySQL における Null 値の複雑さ

導入：

私も含め、MySQL を初めて使用する人は、NULL 値を含むテーブルからクエリを実行すると、予期しない結果が発生する可能性があります。 MySQL では、null は true でも false でもなく不明であり、「IS NULL」または「IS NOT NULL」演算子を使用しない限り、null と null を比較することはできません。 MySQL が null を扱う方法は興味深いだけでなく、理解することが重要であることがわかりました。ここでは、さまざまな MySQL 関数を使用して null がどのように動作するかを示します!

Null 値を含む列を含むテーブルの結合

INNER JOIN を使用した NULL と LEFT JOIN を使用した NULL
以下に説明するように、2 つのテーブルがあるとします。

テーブル名: 教師

id	dept_id	name
101	1	Shrivell
102	1	Throd
103	1	Splint
104	NULL	Spiregrain
105	2	Cutflower
106	NULL	Deadyawn

テーブル名:部門

id	name
1	Computing
2	Design
3	Engineering
NULL	NULL

Teachers テーブルに NULL 値があることに注意してください。 INNER JOIN を使用してこれら 2 つのテーブルを結合するとどうなりますか?

SELECT teachers.name, departments.name AS department
FROM teachers INNER JOIN departments
ON (teachers.dept_id = departments.id)

結果は次のとおりです

+-----------+------------+
| Name      | Department |
+-----------+------------+
| Shrivell  | Computing  |
| Throd     | Computing  |
| Splint    | Computing  |
| Cutflower | Design     |
+-----------+------------+

結果には、Department_id が null の 2 人の教師の名前が表示されないことに注意してください。

しかし、LEFT JOIN を使用する場合はどうでしょうか?見てみましょう。
これは、単に「INNER」を「LEFT」に置き換えたクエリです。

SELECT teachers.name, departments.name AS department
FROM teachers LEFT JOIN departments
ON (teachers.dept_id = departments.id)

結果は次のとおりです

+------------+------------+
| Name       | Department |
+------------+------------+
| Shrivell   | Computing  |
| Throd      | Computing  |
| Splint     | Computing  |
| Spiregrain | NULL       |
| Cutflower  | Design     |
| Deadyawn   | NULL       |
+------------+------------+

すべての教師がリストされていることがわかります。 INNER JOIN は null 値を含むレコードを返しませんが、LEFT JOIN は返します。

なぜ?

NULL 対 NULL で等価演算子 (=) を使用する場合、結果は true でも false でもなく、不明であることに注意してください。したがって、INNER JOIN を使用する場合、比較の両側に NULL 値を持つレコードは結果セットに含まれません。ただし、LEFT JOIN の性質上、dept_id が null であっても、左側のテーブル (この場合は Teachers テーブル) からすべてのレコードが選択されます。このようなレコードをリストから削除する INNER JOIN とは異なり、LEFT JOIN はそれらのレコードを保持します。

存在 & NULL および IN & NULL

EXISTS と IN は両方とも、NULL 値を持つレコードを返しません。
これらのテーブルがある例を考えてみましょう。

テーブル名: customer

customer_id	first_name	last_name	phone_number
1	John	Doe	123-456-7890
2	Jane	Smith	987-654-3210
3	Alice	Johnson	NULL
4	Bob	Brown	555-555-5555

テーブル名: customer_2

customer_id	first_name	last_name	phone_number
1	John	Doe	123-456-7890
2	Jane	Smith	NULL
3	Alice	Johnson	111-222-3333
4	Bob	Brown	NULL

Here is a query:
Using EXISTS to check if a name in b_table exists in a_table.

SELECT * FROM customers AS c1
WHERE EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number); 

Using IN to see if name in b_table is in a_table

SELECT *
FROM customers AS c1
WHERE (c1.first_name, c1.last_name, c1.phone_number) IN (
    SELECT c2.first_name, c2.last_name, c2.phone_number
    FROM customers_2 AS c2
);

Both returns the same result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+
| 1           | John       | Doe       | 123-456-7890 |
+-------------+------------+-----------+--------------+

Notice that records with null values are not returned.

WHY?
Because with EXISTS, you can't link columns that are null. For IN, it ignores null values as if they don't exist. Thus, both treat nulls in the same way: neither returns nulls.

** HOWEVER, it's not the same story with NOT EXISTS and NOT IN when dealing with NULL!**

Let's take a look.
I simply swapped EXISTS for NOT EXISTS.

SELECT * FROM customers AS c1
WHERE NOT EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number);

Here is the result

| customer_id | first_name | last_name | phone_number  |
|-------------|------------|-----------|---------------|
| 2           | Jane       | Smith     | 987-654-3210  |
| 3           | Alice      | Johnson   | [NULL]        |
| 4           | Bob        | Brown     | 555-555-5555  |

Notice that it is returning records with null values. Both Jane and Bob's phone numbers in the customers_2 table are null, but since both phone numbers have values in the customers table, the returned values are not null.

How about using NOT IN?

SELECT *
FROM customers AS c1
WHERE (c1.first_name, c1.last_name, c1.phone_number) NOT IN (
    SELECT c2.first_name, c2.last_name, c2.phone_number
    FROM customers_2 AS c2
);

Here is the result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+

Nothing is returned!
Why does NOT EXISTS return null values but NOT IN does not? It's because they both treat null differently. NOT EXISTS is simply the opposite of EXISTS. It returns EVERYTHING that is not returned by EXISTS, including nulls.

However, NOT IN returns records that evaluate to TRUE, but in MySQL, null is neither TRUE nor FALSE—it is unknown.

Let's take a look at the record for Jane Smith with Id2.
When NOT IN is used, it compares:
Jane != Jane OR Smith != Smith OR 987-654-3210 != null.

Jane != Jane -> False
Smith != Smith -> False
987-654-3210 != null -> NULL
False or False or Null evaluates to NULL, so the record doesn't get returned!

Here's a little twist:
What if the first name in the customers table does not match the first name in the customers_2 table?

Let’s compare:
Jane Smith 987-654-3210 (from the customers table) and Jack Smith null (from the customers_2 table).

Here is the result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+
| 2           | Jack       | Smith     | 987-654-3210 |

What? You see Jack! Doesn't NOT IN fail to evaluate null as either false or true, so the record shouldn't be returned?

Let's analyze it:

Jane != Jack -> True
Smith != Smith -> False
987-654-3210 != null -> NULL

True or False or Null actually returns:
True || False -> True
True || NULL -> True
Therefore, the record gets returned!

If you want to understand how true, false, and null are evaluated, here is the link:

https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_and

As you can see, it can get quite confusing when it comes to comparing null values.

At the end of the day, I think it's best to explicitly compare null values using IS NULL or IS NOT NULL, which returns true or false as shown below.

SELECT * FROM customers AS c1
WHERE EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number OR  (c1.phone_number IS NULL AND c2.phone_number IS NULL));

Conclusion

Handling null values in MySQL can be quite challenging, requiring careful attention when they are present in your database. It's crucial to conduct explicit null checking to clearly define how null values should be treated. By implementing the strategies I mentioned earlier, such as using IS NULL or IS NOT NULL, you can ensure more predictable and accurate query results.

以上がMySQL における Null 値の複雑さの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。