[programmers/SQL] 물고기 종류 별 대어 찾기 : 서브쿼리, JOIN

Level 3 / JOIN

문제 설명 & 요구사항

FISH_INFO 테이블과 FISH_NAME_INFO 테이블이 제공된다.
각 물고기 종류(FISH_TYPE)별로 가장 큰 길이(LENGTH)를 가진 물고기를 조회하는 문제이다.

조회 조건 및 요구 사항은 다음과 같다:

1. 물고기 종류(FISH_TYPE)별로 가장 길이가 긴 물고기를 1마리씩 조회해야 한다.
2. 출력해야 하는 컬럼:
   • 물고기 ID (ID)
   • 물고기 이름 (FISH_NAME)
   • 물고기 길이 (LENGTH)
3. 결과는 ID 오름차순(ASC) 으로 정렬한다.

---

테이블 구조

FISH_INFO

컬럼명	타입	설명
ID	INTEGER	물고기 ID (PK)
FISH_TYPE	INTEGER	물고기 종류 코드
LENGTH	INTEGER	물고기 길이(cm)
TIME	DATETIME	잡힌 시간

---

FISH_NAME_INFO

컬럼명	타입	설명
FISH_TYPE	INTEGER	물고기 종류 코드 (PK)
FISH_NAME	VARCHAR	물고기 이름

---

첫 제출 (오답)

SELECT SUB.FT, FNI.FISH_NAME, SUB.LEN
FROM (
    SELECT F.FISH_TYPE AS FT, MAX(LENGTH) AS LEN
    FROM FISH_INFO AS F
        JOIN FISH_NAME_INFO AS FN
        ON F.FISH_TYPE = FN.FISH_TYPE
    GROUP BY F.FISH_TYPE
) AS SUB
JOIN FISH_NAME_INFO AS FNI
ON SUB.FT = FNI.FISH_TYPE

FT	FISH_NAME	LEN
0	BASS	60
1	SNAPPER	73
2	ANCHOVY	73

각 물고기 타입별로 길이의 최댓값을 서브쿼리로 구한 후 거기서 조건을 사용하려고 했으나, 그 이후 구현하지 못하였다.

그리고 TYPE별 MAX LENGTH를 구할때 서브쿼리에서 굳이 JOIN을 쓸 이유도 없어보인다.

정답 풀이

--서브쿼리
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LEN
    FROM FISH_INFO
    GROUP BY FISH_TYPE

FISH_TYPE	MAX_LEN
0	60
1	73
2	73

먼저 타입별 최대길이를 가지는 서브쿼리를 간단하게 만들고

SELECT F.ID, F.LENGTH
FROM FISH_INFO AS F
JOIN (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LEN
    FROM FISH_INFO
    GROUP BY FISH_TYPE
) AS SUB
    ON F.FISH_TYPE = SUB.FISH_TYPE
    AND F.LENGTH = SUB.MAX_LEN

ID	LENGTH
6	60
8	73
9	73

FISH_INFO 테이블에서 서브쿼리와 JOIN시켜 각 타입별 최대길이를 가진 ID와 LENGTH를 구하였다.

이제 여기서 FISH_NAME만 붙이면 되니 한번더 JOIN하여 해결하였다.

SELECT F.ID, FN.FISH_NAME, F.LENGTH
FROM FISH_INFO AS F
JOIN (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LEN
    FROM FISH_INFO
    GROUP BY FISH_TYPE
) AS SUB
    ON F.FISH_TYPE = SUB.FISH_TYPE
    AND F.LENGTH = SUB.MAX_LEN
-- JOIN 추가
JOIN FISH_NAME_INFO AS FN
    ON F.FISH_TYPE = FN.FISH_TYPE
ORDER BY F.ID

ID	FISH_NAME	LENGTH
6	BASS	60
8	ANCHOVY	73
9	SNAPPER	73

다른풀이: WHERE에서 서브쿼리 사용하여 해결

SELECT F.ID, FN.FISH_NAME, F.LENGTH
FROM FISH_INFO AS  F
    JOIN FISH_NAME_INFO AS FN
    ON F.FISH_TYPE = FN.FISH_TYPE
WHERE F.LENGTH = (
    SELECT MAX(F2.LENGTH)
    FROM FISH_INFO AS F2
    WHERE F2.FISH_TYPE = F.FISH_TYPE
)
ORDER BY F.ID 

JOIN한 테이블에서 각 행마다 WHERE 조건을 검사하고 서브쿼리를 시행한다. ㄴ 이때, 각 행에서의 같은 FISH_TYPE내 최대 길이인 값과 동일한 길이를 WHERE 일치조건으로 걸어 남겨주면, 종류별로 가장 큰 길이의 쿼리를 얻을 수 있다.