[프로그래머스 SQL Lv.3] 업그레이드 할 수 없는 아이템 구하기 (MySQL)

문제링크 : https://school.programmers.co.kr/learn/courses/30/lessons/273712

프로그래머스

 

1. 테이블

(1) ITEM_INFO

Column name	Type	Nullable	Description
ITEM_ID	INTEGER	FALSE	아이템 ID
ITEM_NAME	VARCHAR(N)	FALSE	아이템 명
RARITY	INTEGER	FALSE	아이템의 희귀도
PRICE	INTEGER	FALSE	아이템의 가격

(2) ITEM_TREE

Column name	Type	Nullable	Description
ITEM_ID	INTEGER	FALSE	아이템 ID
PARENT_ITEM_ID	INTEGER	TRUE	PARENT 아이템의 ID

'ITEM_A'->'ITEM_B'와 같이 업그레이드가 가능할 때 'ITEM_A'를 'ITEM_B'의 PARENT 아이템이라고 하며,
PARENT 아이템이 없는 아이템을 ROOT 아이템이라고 한다.

 

2. 문제

(1) 더 이상 업그레이드할 수 없는 아이템의 (2) 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 (3) 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.

 

3. 문제풀이

가장 먼저 (1) 더 이상 업그레이드할 수 없는 아이템이 무엇인지를 정의내려 보자.

예를 들어 'ITEM_A'->'ITEM_B'->'ITEM_C' 와 같이 업그레이드가 가능한 아이템이 있다면 'ITEM_C'의 PARENT 아이템은 'ITEM_B', 'ITEM_B'의 PARENT 아이템은 'ITEM_A' ROOT 아이템은 'ITEM_A'가 된다. 그리고 더 이상 업그레이드할 수 없는 아이템은 'ITEM_C'이다. 

이때 'ITEM_C'는 'ITEM_A'의 PARENT 아이템도, 'ITEM_B'의 PARENT 아이템도 아니다.

즉, 더 이상 업그레이드할 수 없는 아이템이란, 그 어떤 아이템의 PARENT 아이템도 아닌 아이템을 말한다. 다시 말하면 ITEM_TREE의 PARENT_ITEM_ID에 한 번도 등장하지 않는 아이템을 말한다.

(2) 출력해야 하는 컬럼 들 중, 아이템 ID, 아이템의 희귀도는  item_info 테이블에, 아이템 명은 item_tree 테이블에 있으므로 두 테이블을 먼저 조인하자.

SELECT T1.item_id, item_name, rarity  -- (2) 아이템 id, 아이템 명, 희귀도 조회
FROM item_info T1
JOIN item_tree T2
ON T1.item_id = T2.item_id

SELECT문에서 item_name은 item_tree 테이블에만 있는 컬럼명이고, rarity는 item_info에만 있는 컬럼명이므로 시스템이 알아서 해당 테이블의 컬럼으로 인식한다. item_id는 두 테이블 모두에 있는 컬럼명이므로 어떤 테이블의 컬럼인지 명시해 줘야 한다. 

이후 조인한 테이블에서 RARENT_ITEM_ID에 한 번도 등장하지 않는 아이템을 찾는다. 이를 위해 NOT EXISTS 문을 이용한다.

SELECT T1.item_id, item_name, rarity
FROM item_info T1
JOIN item_tree T2
ON T1.item_id = T2.item_id
WHERE NOT EXISTS (  -- item_tree의 parent ID와 같은 item_id가 '존재하지 않는지'를 행별로 확인
    SELECT 1
    FROM item_tree
    WHERE parent_item_id = T1.item_id   -- 상호연관 서브 쿼리(외부 쿼리에 있는 테이블에 대한 참조를 하는 서브 쿼리)
)

(NOT) EXISTS 문은 항상 서브쿼리와 함께 쓰인다.

(3) 결과를 아이템 ID 기준으로 내림차순 정렬한다.

SELECT T1.item_id, item_name, rarity
FROM item_info T1
JOIN item_tree T2
ON T1.item_id = T2.item_id
WHERE NOT EXISTS ( 
    SELECT 1
    FROM item_tree
    WHERE parent_item_id = T1.item_id 
)
ORDER BY T1.item_id DESC  -- item_id 기준 내림차순 정렬

 

< 시행착오 MEMO >
원래 생각했던 코드는 아래와 같다. (간단히 item_id 기준으로만 살펴봄)

SELECT item_id
FROM item_info
WHERE item_id NOT IN (
    SELECT DISTINCT parent_item_id
    FROM item_tree
)

그런데 IN만 있을 때는 결과가 의도한 대로 나왔었는데, NOT IN을 이용하니 아무 결과도 반환되지 않았다.
그래서 찾아보니 NOT IN은 서브쿼리 결과에 NULL이 포함되면 항상 결과를 반환하지 않는다고 한다.

예를 들어 parent_item_id 리스트가 null, 1, 2가 나온다면(중복 제거)
WHERE 절은 내부적으로 아래처럼 평가된다.

item_id <> 1
AND item_id <> 2
AND item_id <> NULL

하지만 이때 "item_id <> NULL"의 결과는 TRUE도 FALSE도 아닌 UNKNOWN이다.
(NULL과의 모든 비교 연산은 UNKNOWN으로 처리)

반면 IN만 있을 때는 내부적으로 아래처럼 평가된다.

item_id = 1
OR item_id = 2
OR item_id = NULL

따라서 NULL은 비교 대상에서 사실상 무시되므로, 상관이 없어보이는 것!

결론은 "not in은 null에 매우 취약"하다!!! (실무에서도 not in보다 not exists를 더 선호한다고...)

 

4. 정답

따라서 정답은

SELECT T1.item_id, item_name, rarity
FROM item_info T1
JOIN item_tree T2
ON T1.item_id = T2.item_id
WHERE NOT EXISTS (  
    SELECT 1
    FROM item_tree
    WHERE parent_item_id = T1.item_id   
)
ORDER BY T1.item_id DESC

 

SQL에서의 null의 의미에 대해서도 나중에 한 번 정리해 봐야겠다.