join 3번, 4번 문제

안녕하세요! 질문해주셔서 감사합니다!! 잘 학습하고 계시네요!

1. join 연습문제 3번: 고향=포켓몬 잡은 위치 같은 것만 구하려면 레프트 조인 쓰지 않고 그냥 아래처럼 이너조인으로 쓰는 게 낫지 않을까요? 레프트 조인을 쓰신 사유가 따로 있으실까요?

이 문제의 경우 레프트 조인이 아닌 이너 조인을 사용하셔도 동일하게 나옵니다. 저는 이너 조인을 쓰다가 헷갈릴 수 있어서 저는 기본적으로 LEFT JOIN을 하고 붙이는 방식을 사용하고 있네요. 두가지 방법 모두 다 이 문제에선 가능합니다!

2. join 연습문제 4번: 저는 아래와 같이 풀었는데 (각 테이블을 서브쿼리에서 where조건으로 범위를 줄여놓고 조인하고자 했습니다) 왜 count(tp.pokemon_id) 나 count(distinct tp.pokemon_id)를 하지 않고 count(tp.id)를 하는지 이해가 잘 안 갑니다..! 5번 문제도 마찬가지로 왜 count(tp.id)인지 동일한 이유로 이해가 잘 안 가요...! 그리고 4번에서 count(tp.id)를 하더라도 아래와 같이 풀면 정답과 같이 water 속성이 나오는데 저는 cnt가 69가 나오네요. (정답에선 14)

    

작성해주신 쿼리를 살펴보니, trainer_pokemon에서 status가 Active, Training인 값만 추출하고 거기에 LEFT JOIN을 하셨네요. 이렇게 할 경우에 trainer_pokemon 테이블에 있는 Active, Training만 남게 됩니다. 여기에서 trainer의 achievement_level이 Master인 값만 뽑아서 LEFT JOIN을 한 것이지요. 이렇게 하고 SELECT *을 해서 데이터를 확인해보면 쉽게 확인할 수 있답니다

확인해보면, archivement_level이 null임을 확인할 수 있습니다. 즉, trainer_pokemon 테이블 중에 trainer 정보가 없는 데이터들이 다 null로 채워지게 됩니다. 그래서 데이터가 뻥튀기가 된 것이지요.

작성해주신 쿼리에서 올바른 답이 나오려면 처음 left join을 inner join으로 하면 archivement_level이 null인 값은 나오지 않을거에요. 또는 JOIN하고 archivement_level IS NOT NULL을 하면 INNER JOIN과 동일한 결과가 나올거에요. 그 후에 실행하면 원하는대로 답이 나옵니다.

지금 쿼리에서 69가 나오는 이유는 tp.id는 모두 값이 존재합니다.(다만 archivement_level은 null) 그래서 69가 나오게 된답니다. 이 때는 t.id를 COUNT하시면 14가 나오게 됩니다

강의에서 제가 tp.id를 사용한 이유는 WHERE 조건을 바깥쪽에서 설정할 경우엔 결국 tp.id와 tp.pokemon_id와 같아지게 됩니다. 그래서 tp.id를 사용했어요.

정리하면
- trainer_pokemon + trainer를 할 때 필터링을 해서 trainer_pokemon에서 NULL인 값이 생기게 됨
- 그 후에 tp.id로 COUNT해서 데이터가 의도와 달라짐(tp.id는 항상 값이 존재하므로)
- JOIN 방식에서 이슈가 생겼다고 보시면 되어요

해결 방법
- 1) trainer_pokemon + trainer를 할 때 INNER JOIN으로 변경
- 2) trainer_pokemon + trainer를 할 때 LEFT JOIN으로 하고 WHERE에 t.archivement_level IS NOT NULL 조건 설정
- 3) 현재 쿼리에서 COUNT를 p.id로 진행

COUNT(컬럼) 할 때 NULL값은 포함하지 않는데, tp.id는 모두 NULL이 아니라 데이터가 더 많게 세어진 것이에요

1) 쿼리

select
 p.type1,
 count(tp.id) as cnt
from (select id,trainer_id, pokemon_id, status from `basic.trainer_pokemon` where status in ('Active', 'Training')) tp
inner join (select id, achievement_level from `basic.trainer` where achievement_level = 'Master') t on tp.trainer_id = t.id
left join (select id, type1 from `basic.pokemon`) p on tp.pokemon_id = p.id
group by 1
order by 2 desc
limit 1

2) 쿼리

select
 p.type1,
 count(tp.id) as cnt
from (select id,trainer_id, pokemon_id, status from `basic.trainer_pokemon` where status in ('Active', 'Training')) tp
left join (select id, achievement_level from `basic.trainer` where achievement_level = 'Master') t on tp.trainer_id = t.id
left join (select id, type1 from `basic.pokemon`) p on tp.pokemon_id = p.id
where t.achievement_level IS NOT NULL
group by 1
order by 2 desc
limit 1

3) 쿼리

select
 p.type1,
 count(p.id) as cnt
from (select id,trainer_id, pokemon_id, status from `basic.trainer_pokemon` where status in ('Active', 'Training')) tp
left join (select id, achievement_level from `basic.trainer` where achievement_level = 'Master') t on tp.trainer_id = t.id
left join (select id, type1 from `basic.pokemon`) p on tp.pokemon_id = p.id
group by 1
order by 2 desc
limit 1

또 궁금한 내용 있으시면 언제든 말씀해주셔요!