[MySQL] NULL 처리

NULL - 결측치 / 0이 아니라 데이터에 값이 없다는 의미NULL 처리 방법

비교연산자(=, <,>)로 NULL을 조회할 수 없다. 

 

IS NULL

- 해당 값이 NULL인 값만 가져오기

SELECT MEMBER_ID, MEMBER_NAME, GENDER
FROM MEMBER_PROFILE
WHERE TLNO IS NULL

 

IS NOT NULL

- 해당 값이 NULL이 아닌 값만 가져오기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL

 

IFNULL()

- 해당 Column값이 NULL을 반환할 때, 다른 값으로 출력할 수 있도록 하는 함수- IFNULL(DATA, '대체 값') 형태

SELECT MEMBER_ID, IFNULL(NAME, 'UNFINED') AS NAME, GENDER
FROM MEMBER_PROFILE

그 외 방법 참고

https://velog.io/@gillog/DB-MySQL-NULL-%EC%B2%98%EB%A6%ACIFNULL-CASE-COALESCE

[DB] MySQL NULL 처리(IFNULL, CASE, COALESCE)

[어떤 방법이 더 효율적인가?]

 

 

문제 예제

https://school.programmers.co.kr/learn/courses/30/lessons/131114

프로그래머스

# IFNULL 사용
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE WAREHOUSE_NAME LIKE '%경기%'
ORDER BY WAREHOUSE_ID

# UNION ALL 사용
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE FREEZER_YN IS NOT NULL AND WAREHOUSE_NAME LIKE '%경기%'
UNION ALL
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, 'N'
FROM FOOD_WAREHOUSE
WHERE FREEZER_YN IS NULL AND WAREHOUSE_NAME LIKE '%경기%'
ORDER BY WAREHOUSE_ID

# COALESCE 사용
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, COALESCE(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE WAREHOUSE_NAME LIKE '%경기%'
ORDER BY WAREHOUSE_ID

# CASE 사용
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS,
CASE WHEN FREEZER_YN IS NULL
THEN 'N' ELSE FREEZER_YN END
FROM FOOD_WAREHOUSE
WHERE WAREHOUSE_NAME LIKE '%경기%'
ORDER BY WAREHOUSE_ID