[MariaDB] NULL 허용 컬럼 사용시 고려할 점

NULL 허용 숫자 자료형 컬럼에 COUNT, AVG 등의 함수를 사용할 경우, 매우 주의를 기울여야 합니다.

다음과 같이 테이블을 생성하고, 자료를 입력한 후에, COUNT, AVG 함수를 이용하여 자료를 조회한 결과를 보면, 결과에 약간씩 차이가 있습니다.

CREATE TABLE `ORDER_DEPT`
(
    `dept_no` INT NOT NULL COMMENT '부서 번호'
    , `order_count` INT NULL DEFAULT NULL COMMENT '주문 수량'
    , `status` CHAR(1) NULL DEFAULT NULL COMMENT '상태정보, C:완료, O:진행중'
)
COMMENT='부서별 주문정보'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `ORDER_DEPT` VALUES (1, NULL, 'C'), (1, 5, 'O'), (1, 5, 'C');
INSERT INTO `ORDER_DEPT` VALUES (1, NULL, 'C'), (1, 6, 'O'), (1, 15, 'O');
INSERT INTO `ORDER_DEPT` VALUES (1, NULL, 'C'), (1, 7, 'C'), (1, 25, 'O');
INSERT INTO `ORDER_DEPT` VALUES (2, NULL, 'C'), (2, 5, 'O'), (2, 5, 'C');
INSERT INTO `ORDER_DEPT` VALUES (2, NULL, 'C'), (2, 6, 'O'), (2, 15, 'O');
INSERT INTO `ORDER_DEPT` VALUES (2, NULL, 'C'), (2, 7, 'C'), (2, 25, 'O');
INSERT INTO `ORDER_DEPT` VALUES (4, NULL, 'C'), (4, 5, 'O'), (4, 5, 'C');
INSERT INTO `ORDER_DEPT` VALUES (4, NULL, 'C'), (4, 6, 'O'), (4, 15, 'O');
INSERT INTO `ORDER_DEPT` VALUES (4, NULL, 'C'), (4, 7, 'C'), (4, 25, 'O');
INSERT INTO `ORDER_DEPT` VALUES (14, NULL, 'C'), (14, 5, 'O'), (14, 5, 'C');
INSERT INTO `ORDER_DEPT` VALUES (14, NULL, 'C'), (14, 6, 'O'), (14, 15, 'O');
INSERT INTO `ORDER_DEPT` VALUES (14, NULL, 'C'), (14, 7, 'C'), (14, 25, 'O');

CREATE INDEX IX_DEPT_NO ON `ORDER_DEPT` ( dept_no );
CREATE INDEX IX_STATUS ON `ORDER_DEPT` ( `status`, dept_no );

SELECT
    dept_no, COUNT(*), COUNT(order_count), COUNT(IFNULL(order_count, 0))
    , AVG(order_count), AVG(IFNULL(order_count, 0)), SUM(order_count)
    , SUM(IFNULL(order_count, 0))
FROM `ORDER_DEPT`
WHERE 1 = 1
    AND dept_no = 1
GROUP BY dept_no;

마지막 조회 쿼리를 실행한 결과는 다음과 같습니다.

+---------+----------+--------------------+-------------------------------+------------------+-----------------------------+------------------+-----------------------------+
| dept_no | COUNT(*) | COUNT(order_count) | COUNT(IFNULL(order_count, 0)) | AVG(order_count) | AVG(IFNULL(order_count, 0)) | SUM(order_count) | SUM(IFNULL(order_count, 0)) |
+---------+----------+--------------------+-------------------------------+------------------+-----------------------------+------------------+-----------------------------+
|       1 |        9 |                  6 |                             9 |          10.5000 |                      7.0000 |               63 |                          63 |
+---------+----------+--------------------+-------------------------------+------------------+-----------------------------+------------------+-----------------------------+

NULL 허용 컬럼에 대하여 COUNT 함수의 동작을 유의하여야 합니다. COUNT와 COUNT()의 결과는 서로 다릅니다. 왜냐하면, COUNT의 경우 NULL 자료는 없는 것이기 때문에 레코드 수에 반영이 되지 않아서, COUNT() 보다 적은 수가 됩니다.

SUM 함수의 경우, NULL 자료는 아무런 영향이 없습니다. SUM)과 같이 하면 오히려 CPU 연산을 한 번 더 해야하기 때문에 성능이 떨어집니다.

AVG 함수의 경우에는 COUNT 함수와 같이 AVG과 AVG )의 결과가 서로 다릅니다. 평균은 합계 / 개수 이므로 당연한 결과입니다.

위와 같이 NULL 허용된 컬럼에 대하여 COUNT 및 AVG 함수 등을 이용할 때는 주의가 필요합니다.