[프로그래머스] 업그레이드 할 수 없는 아이템 구하기 (MySQL) - IN, EXISTS, NULL, Three-valued logic

 

 

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

프로그래머스

문제 요약: ITEM_INFO 테이블의 ITEM_ID가 ITEM_TREE 테이블의 PARENT_ITEM_ID에 없는 데이터를 필터링해 골라내면 된다. 

 

소요시간: ♾️❤️

 

 

공부할 게 많은 문제다.

(1) IN, NOT IN의 특성, EXISTS와의 차이

(2) MySQL에서의 NULL처리

(3) SQL의 삼중논리(Three-valued logic(TVL), 삼진논리라고도 부르는 것 같다)

 

 

 

 

1차시도(틀린 쿼리)

SELECT ITEM_ID, ITEM_NAME, RARITY 
FROM ITEM_INFO 
WHERE ITEM_INFO.ITEM_ID NOT IN (SELECT DISTINCT PARENT_ITEM_ID FROM ITEM_TREE)
ORDER BY ITEM_ID DESC;

이렇게 했더니 필터링이 아예 안 됐다. 서브쿼리만 실행시켜 봤는데 그건 또 멀쩡해서 어디가 문젠지 생각했다가 IN 때문이란 걸 알았다. 아래처럼 아무것도 안 나왔음.

 

 

 

 

(SELECT DISTINCT PARENT_ITEM_ID FROM ITEM_TREE; 의 결과. 편의상 테이블1이라 칭한다)

 

1) WHERE ITEM_INFO.ITEM_ID NOT IN (SELECT PARENT_ITEM_ID FROM 테이블1)을 실행하면

2) ITEM_INFO.ITEM_ID를 테이블1의 모든 데이터와 비교연산을 한다.

3) 그런데 여기서 테이블1의 데이터 중 null이 있으면 → 전체 조건이 UNKNOWN으로 평가된다! 

 

IN 절은 리스트 안의 값 중 하나라도 일치하는지를 검사한다. 그러나 NULL은 알 수 없는 값이므로 어떤 값과도 일치하지 않는다. 따라서 NULL과의 비교는 항상 UNKNOWN 결과를 반환한다😅

 

 

 

🔥SQL의 삼중 논리 체계에서는 조건이 TRUE, FALSE, UNKNOWN 중 하나로 평가된다.

🔥SQL에서는 WHERE 절 뒤에 조건이 UNKNOWN으로 평가되면 해당 조건은 FALSE로 간주되어 결과적으로 아무 데이터도 선택되지 않는다.

 

 

IN과 NOT IN에 대하여 📊 

IN의 처리는 다음과 같다. 

(a = x OR a = y OR ...)

 

따라서, NOT IN의 처리는 다음과 같다.

NOT (a = x OR a = y OR ...) 

또는

(a != x AND a != y AND ...) 

 

 

 

 

두 가지의 예시 쿼리를 살펴보자. 

 

1. IN

SELECT * FROM table WHERE a IN (NULL, 0, 1);

1) 이 쿼리에서 a=1일 때 

• a가 1이므로 조건이 1 IN (NULL, 0, 1)이 됨
• 리스트에 NULL이 포함되어 있지만 1과 1의 비교가 TRUE이므로 전체 조건은 TRUE가 된다! (OR이기 때문)

2) 이 쿼리에서 a=2일 때

• a가 2이므로 조건이 2 IN (NULL, 0, 1)이 됨
• a = NULL OR a = 0 OR a = 1을 연산하면 각각 UNKNOWN, FALSE, FALSE라서 전체 조건이 UNKNOWN으로 평가되고 → 해당 조건이 FALSE로 간주된다.

3) 이 쿼리에서 a가 NULL일 때

• a = null or a = 0 or a = 1을 연산하면 된다. (OR는 하나라도 True가 나오면 뒷 조건을 확인하지 않고 바로 True를 리턴)
• 여기서는 a = null 이 UNKNOWN으로 평가된다. (NULL과의 모든 비교연산은 항상 UNKNOWN)
• 전체 조건이 UNKNOWN으로 평가되고 → 해당 조건이 FALSE로 간주된다. 

 

2. NOT IN

SELECT * FROM table WHERE a NOT IN (NULL, 0, 1);

 

1) 이 쿼리에서 a=1일 때

•  1 !=  NULL AND 1 != 0 AND 1 != 1 의 결과라고 생각하면 편하다. (AND 연산자는 하나라도 False가 나오면 뒷 조건들은 볼 필요도 없이 False를 리턴)
• 1 != 1이 False고 → 바로 전체 조건이 FALSE가 된다.

2) 이 쿼리에서 a=2일 때

• UNKNOWN AND TRUE AND TRUE
• 전체 조건이 UNKNOWN으로 평가되고 → 해당 조건이 FALSE로 간주된다.

결론: IN을 쓰려면 데이터에 NULL이 섞여있을 경우에 대비해 별도 처리를 해줘야 한다.

 

 

정답1 

SELECT ITEM_ID, ITEM_NAME, RARITY 
FROM ITEM_INFO I
WHERE I.ITEM_ID NOT IN (SELECT DISTINCT PARENT_ITEM_ID FROM ITEM_TREE WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY ITEM_ID DESC;

 

 

 

 

 

 

Q. 그렇다면 EXISTS를 활용할 수는 없을까?

A. 있다. IN은 모든 데이터와 하나하나 매칭해 비교해야 하는 반면, EXISTS는 한 조건의 True/False만 확인하기 때문에 더 빠르다. 

→ 대용량 데이터 처리에는 EXISTS를 쓰는 게 더 좋다.

또한, EXISTS는 별도 NULL처리가 필요없다는 장점도 있다.

 

정답 2

WITH UNIQUE_PARENTS AS (
    SELECT DISTINCT PARENT_ITEM_ID FROM ITEM_TREE)

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO I
WHERE NOT EXISTS (SELECT * FROM UNIQUE_PARENTS UP WHERE UP.PARENT_ITEM_ID = I.ITEM_ID)
ORDER BY ITEM_ID DESC;

 

 

 

 

 

 

 

 

 

 

 

 

참고

https://docs.snowflake.com/ko/sql-reference/ternary-logic