[프로그래머스 IS NULL] #3. NULL 처리하기(IFNULL(), NVL())

https://programmers.co.kr/learn/courses/30/lessons/59410?language=mysql 

코딩테스트 연습 - NULL 처리하기

 

문제 설명

 

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

 

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

 

Q) 입양 게시판에 동물 정보를 게시하려 합니다.

동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요.

이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

 

예시

 

예를 들어 ANIMAL_INS 테이블이 다음과 같다면

 

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A350276	Cat	2017-08-13 13:50:00	Normal	Jewel	Spayed Female
A350375	Cat	2017-03-06 15:01:00	Normal	Meo	Neutered Male
A368930	Dog	2014-06-08 13:20:00	Normal	NULL	Spayed Female

마지막 줄의 개는 이름이 없기 때문에, 이 개의 이름은 "No name"으로 표시합니다. 따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.

 

ANIMAL_TYPE	NAME	SEX_UPON_INTAKE
Cat	Jewel	Spayed Female
Cat	Meo	Neutered Male
Dog	No name	Spayed Female

※ 컬럼 이름은 일치하지 않아도 됩니다.

 

풀이 1. MySQL

-- 코드를 입력하세요
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') as NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

• IFNULL()함수를 사용하여 NAME이 NULL이면 No name을 출력하도록 한다.

풀이 2. Oracle

SELECT ANIMAL_TYPE, NVL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• MySQL의 IFNULL()함수와 같은 의미인 NVL() 함수를 써서 NAME이 NULL이면 No name을 출력하도록 한다.

SELECT ANIMAL_TYPE, 
        CASE WHEN NAME IS NULL THEN 'No name'
            ELSE NAME
        END AS NAME,
        SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

• NVL() 함수 말고 CASE ~ WHEN ~ THEN ~ ELSE ~ END 구문을 쓰는 방법도 있다.
• CASE WHEN NAME IS NULL : NAME이 NULL이면
• THEN 'No name' : No name으로 출력
• ELSE NAME : NULL이 아니라면 NAME 값 출력

실행 결과

 

ANIMAL_TYPE	NAME	SEX_UPON_INTAKE
Cat	Sugar	Neutered Male
Cat	Jewel	Spayed Female
Cat	Meo	Neutered Male
Dog	Harley	Spayed Female
Cat	Gia	Spayed Female
Dog	Peanutbutter	Neutered Male
Dog	Bj	Neutered Male
Cat	Tux	Neutered Male
Cat	Ariel	Spayed Female
Dog	Kia	Spayed Female
Dog	Sammy	Neutered Male
Dog	Faith	Spayed Female
Dog	Shadow	Neutered Male
Dog	Elijah	Neutered Male
Dog	Queens	Spayed Female
Dog	Puppy	Neutered Male
Dog	Happy	Neutered Male
Dog	Fuzzo	Neutered Male
Dog	Simba	Neutered Male
Dog	Baby Bear	Neutered Male
Dog	Stitch	Neutered Male
Dog	*Morado	Neutered Male
Dog	Girly Girl	Spayed Female
Dog	Honey	Spayed Female
Dog	Goofy	Neutered Male
Dog	Hugo	Neutered Male
Dog	Diablo	Neutered Male
Dog	Minnie	Spayed Female
Dog	Miller	Neutered Male
Dog	Cookie	Spayed Female
Dog	Woody	Neutered Male
Dog	Stormy	Spayed Female
Dog	No name	Spayed Female
Dog	Sniket	Neutered Male
Cat	Emily	Spayed Female
Dog	Katie	Spayed Female
Cat	Greg	Neutered Male
Dog	Ceballo	Neutered Male
Dog	Sailor	Neutered Male
Dog	April	Spayed Female
Cat	Ella	Spayed Female
Dog	Rosie	Spayed Female
Dog	Dash	Neutered Male
Dog	Mama Dog	Spayed Female
Dog	Dora	Spayed Female
Dog	Lucy	Spayed Female
Dog	Frijolito	Neutered Male
Dog	Lyla	Intact Female
Dog	Zoe	Spayed Female
Dog	Mercedes	Spayed Female
Dog	Disciple	Intact Male
Dog	Pickle	Spayed Female
Dog	Scooby	Neutered Male
Dog	Laika	Spayed Female
Dog	Ruby	Spayed Female
Dog	Pepper	Spayed Female
Dog	Cherokee	Neutered Male
Dog	Maxwell 2	Intact Male
Dog	Princess	Spayed Female
Cat	Oreo	Neutered Male
Dog	Finney	Neutered Male
Cat	Jj	Neutered Male
Cat	Jedi	Neutered Male
Dog	Clyde	Neutered Male
Dog	Giovanni	Neutered Male
Cat	Tiko	Neutered Male
Dog	Punch	Neutered Male
Dog	Goldie	Spayed Female
Dog	Dakota	Spayed Female
Dog	Spider	Neutered Male
Dog	Blaze	Neutered Male
Dog	Holly	Spayed Female
Dog	Rome	Neutered Male
Dog	Nellie	Spayed Female
Dog	Penny	Spayed Female
Dog	Skips	Neutered Male
Dog	Chip	Neutered Male
Dog	Snickerdoodl	Spayed Female
Dog	Rogan	Neutered Male
Dog	Raven	Spayed Female
Dog	Charlie	Neutered Male
Dog	Lucy	Spayed Female
Dog	Jack	Neutered Male
Dog	Reggie	Neutered Male
Dog	Lucy	Spayed Female
Dog	Anna	Spayed Female
Dog	Kaila	Spayed Female
Dog	Sabrina	Spayed Female
Dog	Jake	Neutered Male
Dog	Lizzie	Spayed Female
Dog	Stanley	Neutered Male
Dog	Chewy	Intact Female
Cat	Raven	Spayed Female
Cat	Mitty	Spayed Female
Dog	Jimminee	Neutered Male
Dog	*Sam	Neutered Male
Dog	Jackie	Neutered Male
Dog	Benji	Spayed Female
Dog	Shelly	Spayed Female
Dog	Rocky	Neutered Male