[프로그래머스] 👩🏻‍💻 (MySQL) IS NULL 관련 문제 풀이, ⭐ 업그레이드 할 수 없는 아이템 구하기 외

 

[목차]

1. 경기도에 위치한 식품창고 목록 출력하기

2. 이름이 없는 동물의 아이디 / 이름이 있는 동물의 아이디

3. NULL 처리하기

4. 나이 정보가 없는 회원 수 구하기

5. ROOT 아이템 구하기

6. 잡은 물고기의 평균 길이 구하기

7. ⭐업그레이드 할 수 없는 아이템 구하기

---

1. 경기도에 위치한 식품창고 목록 출력하기

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

---

2. 이름이 없는 동물의 아이디 & 이름이 있는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID;

 

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

---

3. NULL 처리하기

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

---

4. 나이 정보가 없는 회원 수 구하기

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE AGE IS NULL;

---

5. ROOT 아이템 구하기

SELECT A.ITEM_ID, A.ITEM_NAME
FROM ITEM_INFO A JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IS NULL
ORDER BY A.ITEM_ID;

---

6. 잡은 물고기의 평균 길이 구하기

SELECT ROUND(
    AVG(
        CASE 
            WHEN LENGTH IS NULL THEN 10  -- NULL 값인 경우 10으로 처리
            WHEN LENGTH <= 10 THEN 10   -- 10 이하일 경우 10으로 처리
            ELSE LENGTH                 -- 그 외의 경우에는 원래 LENGTH 사용
        END
    ), 2) AS AVERAGE_LENGTH
FROM FISH_INFO

 

보기 좋게 코드 정리를 좀 했다.

이 문제는 생각이 좀 필요했다.

 

처음에는 계속 틀려서 오류가 났다.

WHEN LENGTH IS NULL THEN 10  -- NULL 값인 경우 10으로 처리

이 문장을 빼먹었기 때문이다. 문제를 잘 보면 10cm 이하의 물고기들은 길이를 NULL로 표시한다고 했기 때문에

LENGTH가 NULL이라고 해도 결국은 길이를 가지고 있기 때문에 AVG 계산에서 NULL값이 제외되어서는 안 된다. NULL도 잡힌 데이터도 결국 10으로 치환을 해서 평균 계산을 수행하는데 포함이 될 수 있도록 해 주어야 한다.

 

그런데 자세히 생각해보면

그럼 굳이 LENGTH <= 10 THEN 10을 해 줄 필요가 없잖아?

어차피 LENGTH <= 10 는 NULL이니까 NULL 처리만 해 주면 되는 간단한 문제이다.

그럼 코드가 정말 짧아진다. 

아래 코드도 정답이다. 

SELECT ROUND(AVG(IFNULL(LENGTH, 10)),2) AS AVERAGE_LENGTH FROM FISH_INFO

---

7. ⭐업그레이드 할 수 없는 아이템 구하기

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO A LEFT JOIN ITEM_TREE B ON A.ITEM_ID = B.PARENT_ITEM_ID
WHERE B.PARENT_ITEM_ID IS NULL
ORDER BY A.ITEM_ID DESC;

 

 

LEFT 조인을 해 줘서 ITEM_INFO 테이블의 데이터가 다 나오게 한 상태에서

ITEM_INFO의 ITEM_ID와 ITEM_TREE의 PARENT_ITEM_ID를 조언해서

PARENT_ITEM_ID가 널 값인 행을 조회하면 된다.

 

아니면 NOT EXISTS를 써 주면 된다.

EXIST에 S 붙는거 주의하자

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO A
JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID
WHERE NOT EXISTS (
    SELECT 1
    FROM ITEM_TREE B2
    WHERE B2.PARENT_ITEM_ID = A.ITEM_ID
)
ORDER BY A.ITEM_ID DESC;

 

 

하지만 위처럼 올바른 쿼리를 짜기까지 약간의 시행착오가 있었다.

어떤 부분이 문제되었는지 남겨 본다. 

 

처음에 짠 쿼리

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO A JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID
WHERE A.ITEM_ID NOT IN (B.PARENT_ITEM_ID) 
ORDER BY A.ITEM_ID DESC;

 

처음에는 그냥 ITEM_ID 기준으로 조인을 하고

ITEM_INFO의 아이디가 ITEM_TREE에서 PARENT_ITEM_ID 컬럼에만 없으면 되는거 아닌가?

라고 생각 했는데 새로 알게된 사실은 NOT IN은 뒤에 오는 컬럼에 NULL 값이 있으면 데이터를 제대로 걸러내지 못한다는 것이다. 

 

만약 B.PARENT_ITEM_ID에 NULL이 포함되어 있으면 어떻게 될까? A.ITEM_ID가 NULL이 포함된 리스트에서 NOT IN 조건을 평가할 때, SQL은 A.ITEM_ID와 NULL을 비교하는 것이 불확실하므로 결과가 예상과 다를 수 있다고 한다. 예를 들어, A.ITEM_ID = 100일 때 NOT IN에서 NULL 값이 포함되면, `100 NOT IN (NULL)`은 비교가 불가능한 불확실한 상태라는 것이다.

 

그래서 나는 AND B.PARENT_ITEM_ID IS NOT NULL 조건을 추가 해 주었다.

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO A
JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID
WHERE A.ITEM_ID NOT IN (B.PARENT_ITEM_ID) AND B.PARENT_ITEM_ID IS NOT NULL
ORDER BY A.ITEM_ID DESC;

 

이것도 정답처리가 되지 않는다.

예시테이블을 보면서 잘 생각해보니... 이걸 추가해 주고 말고 할 게 아니었다.

 

NOT IN과 NULL 처리 문제를 해결하려는 '시도'는 있었으나

여전히 NOT IN을 사용하기 때문에 NULL 값이 포함된 컬럼과의 비교에서 문제가 발생한다.

B.PARENT_ITEM_ID IS NOT NULL 조건을 추가한 것이 문제가 아니고, 기본적으로 NOT IN 자체의 특성 때문이다.