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

출처: 프로그래머스 스쿨

문제 설명

• 게임 내 아이템은 업그레이드가 가능한 경우가 있음
  • 'ITEM_A'->'ITEM_B'->'ITEM_C' 와 같이 업그레이드가 가능할 때
  • 'ITEM_C'의 PARENT 아이템은 'ITEM_B'
  • 'ITEM_B'의 PARENT 아이템은 'ITEM_A'
  • PARENT 아이템이 없는 'ITEM_A'는 ROOT 아이템이라고 명명
• 각 아이템들은 오직 하나의 PARENT 아이템 ID 를 가지며, ROOT 아이템의 PARENT 아이템 ID 는 NULL임
• ROOT 아이템이 없는 경우는 존재하지 않음

테이블 정보

• ITEM_INFO: 해당 게임에서 사용되는 아이템 정보
  

ITEM_ID	ITEM_NAME	RARITY
0	ITEM_A	RARE
1	ITEM_B	RARE
2	ITEM_C	LEGEND
3	ITEM_D	RARE
4	ITEM_E	RARE

→ ITEM_ID, ITEM_NAME, RARITY, PRICE는 각각 아이템 ID, 아이템 명, 아이템의 희귀도, 아이템의 가격을 나타냄

Column name	Type	Nullable
ITEM_ID	INTEGER	FALSE
ITEM_NAME	VARCHAR(N)	FALSE
RARITY	INTEGER	FALSE
PRICE	INTEGER	FALSE

• ITEM_TREE: 아이템 사이 관계

ITEM_ID	PARENT_ITEM_ID
0	NULL
1	0
2	0
3	1
4	1

ITEM_ID, PARENT_ITEM_ID는 각각 아이템 ID, PARENT 아이템의 ID를 나타냅니다.

Column name	Type	Nullable
ITEM_ID	INTEGER	FALSE
PARENT_ITEM_ID	INTEGER	TRUE

문제

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

위의 테이블을 예시로 들자면 'ITEM_A' 는 'ITEM_B', 'ITEM_C' 로 업그레이드가 가능하며 'ITEM_B' 는 'ITEM_D', 'ITEM_E' 로 업그레이드가 가능합니다. 'ITEM_C', 'ITEM_D', 'ITEM_E' 는 더 이상 업그레이드가 가능하지 않으므로 결과는 다음과 같이 나와야 합니다.

ITEM_ID	ITEM_NAME	RARITY
4	ITEM_E	RARE
3	ITEM_D	RARE
2	ITEM_C	LEGEND

풀이 과정

• 포인트
  • 아이템은 오직 하나의 parent item id를 가짐
  • root item의 parent item id는 NULL임
  • 더 이상 업그레이드 할 수 없는 아이템 정보를 출력

SELECT item_id, item_name, rarity
FROM item_info
WHERE item_id NOT IN(
    SELECT DISTINCT parent_item_id
    FROM item_tree
    WHERE parent_item_id IS NOT NULL)
ORDER BY item_id DESC

• 풀이 중 생각한 것
  • parent_item_id에는 중복이 있으니 DISTINCT를 써야겠다.
  • "더 이상"이니까 애초에 업그레이드 할 수 없는 root item (parent_item_id가 NULL)은 제외해야겠다.
    • 알고보니 parent_item_id IS NOT NULL을 사용하는 다른 이유가 있었다.

parent_item_id 에 대한 NULL 체크가 필요한 이유

• parent_item_id 에 대한 NULL 체크를 하지 않을 경우 아무런 결과값이 나오지 않는다고 함
• 주목할 부분은 NOT IN 구문의 동작 구조
  • IN은 뒤따라 괄호로 주어지는 값들 중 하나라도 == 비교를 만족하면 해당 데이터를 출력(OR 연산과 같음)
  • 그러나 NOT IN은 뒤따라 괄호로 주어지는 값들 모두에 대해 != 비교를 만족해야 해당 데이터를 출력 (AND 연산과 같음)
• NOT IN (1, 2, NULL) 일 경우 데이터가 출력되려면 1도 아니고 2도 아니고 NULL도 아니어야 한다는 뜻

"item_id NOT IN (1, 2, NULL) 일 경우, item_id가 3이면 해당 데이터는 출력되어야 하는 것이 아니냐"는 질문이 있을 수 있는데 해답의 실마리는 NULL과의 비교는 무조건 false라는 점임.
3은 1도 아니고 2도 아니고 NULL도 아니지만 "논리학적"으로 접근했을 때 3과 NULL의 비교 결과값은 false → NOT IN의 AND 연산에서 결과적으로 false임 → 아무런 데이터도 반환되지 못함

• JOIN을 이용해서도 풀 수 있다.

SELECT a.item_id, item_name, rarity
FROM item_info a LEFT JOIN item_tree b ON a.item_id = b.parent_item_id
WHERE b.item_id IS NULL
ORDER BY a.item_id DESC

※ LEFT JOIN

ITEM_ID	ITEM_NAME	RARITY
0	ITEM_A	RARE
1	ITEM_B	RARE
2	ITEM_C	LEGEND
3	ITEM_D	RARE
4	ITEM_E	RARE

LEFT JOIN: item_info의 레코드는 전부 표기, item_tree는 parents_item_id 있는 경우만 값이 들어감

ITEM_ID	PARENT_ITEM_ID
0	NULL
1	0
2	0
3	1
4	1

JOIN 결과

ITEM_ID	ITEM_NAME	RARITY	PRICE	b.ITEM_ID
0	ITEM_A	RARE	10000	1
0	ITEM_A	RARE	10000	2
1	ITEM_B	RARE	9000	3
1	ITEM_B	RARE	9000	4
2	ITEM_C	LEGEND	11000	NULL
3	ITEM_D	RARE	10000	NULL
4	ITEM_E	RARE	12000	NULL

추가 공부: <> vs. IS NOT

• <>은 산술 연산자

• SQL에서는 NULL 값을 =, <> 같은 비교 연산자를 이용해 비교할 수 없다.
  w3schools
  stackoverflow

• SQL에서 NULL이란

  • 데이터 값이 존재하지 않음을 나타내기 위한 키워드(marker)
  • 모든 관계형 데이터베이스 시스템(RDBMS)이 누락된 정보나 적용할 수 없는 정보의 표현을 지원해야 한다는 요구 사항을 충족하는 역할
  • 특정한 값이 아닌 '값이 없음'에 대한 표시
  • 주의: 어떤 연산자를 사용하든 항상 NULL이라는 건 아님
    SELECT 100*NULL; → 연산 결과: NULL
    SELECT NULL / 10; → 연산 결과: NULL
    SELECT CONCAT('Hello ', NULL, 'World'); → 연산 결과: NULL
    SELECT NULL || TRUE → 연산 결과: 1

마지막 예시 관련 DBeaver 안내 메시지: '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead