Die Feinheiten von Nullwerten in MySQL

Einführung:

Für diejenigen, die neu bei MySQL sind, darunter auch ich, kann es zu unerwarteten Ergebnissen kommen, wenn Sie eine Abfrage aus einer Tabelle durchführen, die einige Nullwerte enthält. In MySQL ist null weder wahr noch falsch, sondern unbekannt, und Sie können null nicht mit null vergleichen, es sei denn, Sie verwenden den Operator „IS NULL“ oder „IS NOT NULL“. Ich fand, dass die Art und Weise, wie MySQL Null behandelt, nicht nur interessant, sondern auch wichtig zu verstehen ist. Hier demonstriere ich, wie sich Null mit verschiedenen MySQL-Funktionen verhält!

Tabellen mit Spalten verbinden, die Nullwerte enthalten

NULL mit INNER JOIN vs. NULL mit LEFT JOIN
Nehmen wir an, es gibt zwei Tabellen, wie unten beschrieben:

Tabellenname: Lehrer

Tabellenname: Abteilungen

Beachten Sie, dass die Lehrertabelle Nullwerte enthält. Was würde passieren, wenn ich einen INNER JOIN verwende, um diese beiden Tabellen zu kombinieren?

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

Hier ist das Ergebnis

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

Beachten Sie, dass Sie in den Ergebnissen nicht die Namen von zwei Lehrern sehen, deren Abteilungs-IDs null sind.

Aber wie wäre es mit der Verwendung eines LEFT JOIN? Werfen wir einen Blick darauf.
Hier ist die Abfrage, bei der ich einfach „INNER“ durch „LINKS“ tausche.

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

Hier ist das Ergebnis

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

Sie werden feststellen, dass alle Lehrer aufgelistet sind. INNER JOIN gibt keine Datensätze mit Nullwerten zurück, LEFT JOIN hingegen schon.

WARUM?

Bedenken Sie, dass bei Verwendung des Gleichheitsoperators (=) mit NULL zu NULL das Ergebnis weder wahr noch falsch ist – es ist unbekannt. Daher werden Datensätze mit NULL-Werten auf beiden Seiten des Vergleichs bei Verwendung von INNER JOIN nicht in die Ergebnismenge einbezogen. Aufgrund der Natur von LEFT JOIN werden jedoch ALLE Datensätze aus der linken Tabelle (in diesem Fall der Lehrertabelle) ausgewählt, auch wenn die dept_id null ist. Im Gegensatz zu INNER JOIN, das solche Datensätze aus der Liste entfernen würde, behält LEFT JOIN sie bei.

EXISTS & NULL und IN & NULL

Sowohl EXISTS als auch IN geben keine Datensätze mit Nullwerten zurück.
Betrachten wir das Beispiel, in dem ich diese Tabellen habe.

Tabellenname: Kunden

Tabellenname: customer_2

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.

Das obige ist der detaillierte Inhalt vonDie Feinheiten von Nullwerten in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!