[SQL][프로그래머스] 경기도에 위치한 식품창고 목록 출력하기 - IFNULL + ISNULL + CASE WHEN THEN END AS + COALESCE MYSQL

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

프로그래머스

코드설명

IFNULL + ISNULL + CASE WHEN THEN END AS + COALESCE 을 활용하는 문제입니다.

 

문제에서 경기도의 창고를 찾는것이 목표이니 LIKE를 활용하여 경기도를 검색합니다.

 

 

코드

SELECT 
WAREHOUSE_ID, 
WAREHOUSE_NAME, ADDRESS, 
IFNULL(FREEZER_YN, 'N') AS FREEZER_YN FROM FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID ASC;

 

혹은 CASE WHEN 을 사용하고 싶다면 아래와 같이 해결할 수도 있습니다.

SELECT 
WAREHOUSE_ID, 
WAREHOUSE_NAME, ADDRESS, 
CASE 
    WHEN FREEZER_YN = 'Y' THEN 'Y'
    WHEN FREEZER_YN = 'N' THEN 'N'
    WHEN FREEZER_YN IS NULL THEN 'N'
    ELSE 'N'
END AS FREEZER_YN 
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID ASC;

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, 
    CASE 
        WHEN FREEZER_YN IS NULL THEN 'N'
        ELSE FREEZER_YN
    END AS 'FREEZER_yN'
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID ASC;

 

SELECT 
WAREHOUSE_ID,
WAREHOUSE_NAME,
ADDRESS,
COALESCE(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID ASC