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
아래에 설명된 것처럼 두 개의 테이블이 있다고 가정해 보겠습니다.

테이블 이름 : 선생님

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

교사 테이블에 null 값이 있다는 점에 유의하세요. INNER JOIN을 사용하여 두 테이블을 결합하면 어떻게 되나요?

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인 두 교사의 이름이 표시되지 않습니다.

하지만 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이더라도 왼쪽 테이블(이 경우 Teacher 테이블)에서 모든 레코드를 선택합니다. 목록에서 해당 레코드를 제거하는 INNER JOIN과 달리 LEFT JOIN은 해당 레코드를 유지합니다.

EXISTS & NULL 및 IN & NULL

EXISTS와 IN 모두 null 값이 있는 레코드를 반환하지 않습니다.
이러한 테이블이 있는 예를 살펴보겠습니다.

테이블 이름: 고객

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!