[MySQL] 3.8 데이터 필터링 - IS NULL

---

http://www.mysqltutorial.org 내용을 따라 익히며 정리한 글입니다. 예시에 나오는 데이터셋은 MySQL 샘플 데이터셋인 classicmodels DB입니다.

---

1. IS NULL 소개

값이 NULL 인지 테스트 하려면 다음과 같이 IS NULL 을 사용한다

value IS NULL

값이 NULL 이면 TRUE 를, 아니면 FALSE 를 리턴한다.

MySQL에는 불리언 타입이 내장되어 있지 않다. 대신 TINYINT(1) 를 써서 참트루 일 경우 1, FALSE 면 0으로 표시한다.

IS NULL 은 비교연산자이기 때문에 SELECT 나 WHERE 절에서 쓸 수 있다.

SELECT 1 IS NULL,
       0 IS NULL,  
       NULL IS NULL;

+-----------+-----------+--------------+
| 1 IS NULL | 0 IS NULL | NULL IS NULL |
+-----------+-----------+--------------+
|         0 |         0 |            1 |
+-----------+-----------+--------------+
1 row in set (0.00 sec)

값이 NULL 이 아닌지 확인하고 싶다면 하려면 IS NOT NULL 을 사용한다

value IS NOT NULL

값이 NULL 이 아니면 TRUE(1) 를, 값이 NULL 이면 FALSE(0) 을 리턴한다

SELECT 1 IS NOT NULL,
       0 IS NOT NULL,
       NULL IS NOT NULL;

+---------------+---------------+------------------+
| 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL |
+---------------+---------------+------------------+
|             1 |             1 |                0 |
+---------------+---------------+------------------+
1 row in set (0.00 sec)

2. IS NULL 예시

customers 테이블에서 salesrepemployeenumber 가 없는 데이터들의 고객명, 국가, salesrepemployeenumber 을 가져와 보겠다.

SELECT
    customerName,
    country,
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL
ORDER BY customerName
LIMIT 5;

+----------------------------+-------------+------------------------+
| customerName               | country     | salesrepemployeenumber |
+----------------------------+-------------+------------------------+
| ANG Resellers              | Spain       |                   NULL |
| Anton Designs, Ltd.        | Spain       |                   NULL |
| Asian Shopping Network, Co | Singapore   |                   NULL |
| Asian Treasures, Inc.      | Ireland     |                   NULL |
| BG&E Collectables          | Switzerland |                   NULL |
+----------------------------+-------------+------------------------+
5 rows in set (0.00 sec)

salesrepemployeenumber 가 있는 데이터들을 가져오려면 IS NULL 대신 IS NOT NULL 을 써준다.

SELECT
    customerName,
    country,
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NOT NULL
ORDER BY customerName
LIMIT 5;

+-------------------------+-----------+------------------------+
| customerName            | country   | salesrepemployeenumber |
+-------------------------+-----------+------------------------+
| Alpha Cognac            | France    |                   1370 |
| American Souvenirs Inc  | USA       |                   1286 |
| Amica Models & Co.      | Italy     |                   1401 |
| Anna's Decorations, Ltd | Australia |                   1611 |
| Atelier graphique       | France    |                   1370 |
+-------------------------+-----------+------------------------+
5 rows in set (0.00 sec)

3. IS NULL 최적화

IS NULL 은 연산자 = 과 같은 방식으로 최적화를 수행한다.

SELECT
    customerNumber,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL
LIMIT 5;

+----------------+------------------------+
| customerNumber | salesRepEmployeeNumber |
+----------------+------------------------+
|            125 |                   NULL |
|            169 |                   NULL |
|            206 |                   NULL |
|            223 |                   NULL |
|            237 |                   NULL |
+----------------+------------------------+
5 rows in set (0.00 sec)

다음은 쿼리의 EXPLAIN 이다.

EXPLAIN SELECT
    customerNumber,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;

+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | ref  | salesRepEmployeeNumber | salesRepEmployeeNumber | 5       | const |   22 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

MySQL은 col = value OR col IS NULL 조합도 최적화할 수 있다. 다음의 예를 보면, 최적화가 적용됐을 경우 EXPLAIN 에서 ref_or_null 을 볼 수 있다.

EXPLAIN SELECT
    customerNumber,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber = 1370 OR
    salesRepEmployeeNumber IS NULL;

+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type        | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | customers | NULL       | ref_or_null | salesRepEmployeeNumber | salesRepEmployeeNumber | 5       | const |   29 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------------+------------------------+------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)