데이터베이스 작업을 하다 보면 특정 열에 빈 문자열('')이나 NULL 값이 섞여 있는 경우가 많습니다. 이럴 때 해당 열의 값이 비어 있거나 NULL이라면, 같은 행에 있는 다른 열의 값으로 대체하여 의미 있는 데이터만 조회하는 방법을 알아보겠습니다.
1. 테이블 생성하기
먼저 예제로 사용할 테이블을 생성합니다.
mysql> create table DemoTable839(
StudentFirstName varchar(100),
StudentLastName varchar(100)
);
Query OK, 0 rows affected (0.69 sec)2. 샘플 데이터 삽입하기
INSERT 명령어를 사용해 테스트용 레코드를 몇 개 삽입합니다. 여기서는 일부러 빈 문자열과 NULL 값을 포함시켰습니다.
mysql> insert into DemoTable839 values('Chris','Brown');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable839 values('','Taylor');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable839 values(NULL,'Taylor');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable839 values('Adam','Smith');
Query OK, 1 row affected (0.12 sec)3. 전체 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable839;
실행 결과는 다음과 같습니다. StudentFirstName 열에 빈 값과 NULL 값이 포함되어 있는 것을 확인할 수 있습니다.
+------------------+-----------------+ | StudentFirstName | StudentLastName | +------------------+-----------------+ | Chris | Brown | | | Taylor | | NULL | Taylor | | Adam | Smith | +------------------+-----------------+ 4 rows in set (0.00 sec)
4. IF() 함수와 LENGTH() 함수로 값 대체하기
아래 쿼리는 StudentFirstName 열의 값이 비어 있거나 NULL인 경우, 같은 행의 StudentLastName 값으로 대체하여 반환합니다.
mysql> select if(length(StudentFirstName),StudentFirstName,StudentLastName) from DemoTable839;
실행 결과는 다음과 같습니다.
+---------------------------------------------------------------+ | if(length(StudentFirstName),StudentFirstName,StudentLastName) | +---------------------------------------------------------------+ | Chris | | Taylor | | Taylor | | Adam | +---------------------------------------------------------------+ 4 rows in set (0.00 sec)
동작 원리 설명
이 쿼리가 작동하는 원리를 살펴보면 다음과 같습니다.
- length(StudentFirstName): 문자열의 길이를 반환합니다. 빈 문자열('')은 길이가 0이고, NULL은 length() 적용 시 NULL을 반환합니다.
- if(조건, 참일 때 값, 거짓일 때 값): 조건이 참(0이 아니고 NULL이 아닌 경우)이면 두 번째 인자를, 그렇지 않으면 세 번째 인자를 반환합니다.
따라서 StudentFirstName에 실제 값이 있으면 그대로 반환되고, 빈 문자열이나 NULL이면 StudentLastName 값으로 대체됩니다.
참고: COALESCE()와 NULLIF() 조합
NULL 값만 처리한다면 COALESCE() 함수를 사용할 수도 있습니다. 하지만 COALESCE()는 빈 문자열('')은 NULL로 간주하지 않으므로, 빈 문자열까지 함께 처리하려면 위에서 사용한 if(length(...)) 방식이 더 적합합니다.
-- NULL만 처리하는 경우 SELECT COALESCE(StudentFirstName, StudentLastName) FROM DemoTable839; -- 빈 문자열과 NULL을 모두 처리하는 경우 SELECT COALESCE(NULLIF(StudentFirstName, ''), StudentLastName) FROM DemoTable839;
상황에 맞는 방법을 선택하면 됩니다. 이처럼 MySQL의 조건 함수를 활용하면 불완전한 데이터를 한 번의 쿼리로 깔끔하게 정리할 수 있습니다.