[Lv.3] ★ 물고기 종류 별 대어 찾기 [subQuery, WHERE, JOIN]

문제 해석

 

1. FISH_NAME_INFO에 있는 물고기들을 FISH_TYPE에 따라 가장 큰 물고기 길이를 반환할 것.

2. 물고기 ID에 대해 오름차순 정렬할 것

 

 

문제 해결을 위한 개념 정리

 

✅ where 

아래 자료 참고

이름에 el이 들어가는 동물 찾기 [where]

 

✅ JOIN

아래 자료 참고

과일로 만든 아이스크림 고르기 [LEFT JOIN]

 

✅ SUB QUERY

✅ 쿼리문 안의 쿼리문으로, SQL에서는 변수 설정 등의 기능이 불가능하기 때문에 서브쿼리를 통해 필터링 혹은 값을 전달하기 위해 많이 사용된다

 

1️⃣ SELECT 절 서브쿼리

⇒ 서브쿼리의 결과를 하나의 컬럼처럼 사용

# 고객 id와 이름을 출력하고, 각 고객의 리뷰 수를 계산한 열 추가
SELECT 
    M.MEMBER_ID, 
    M.MEMBER_NAME, 
    (
        SELECT COUNT(*) 
        FROM REST_REVIEW R 
        WHERE R.MEMBER_ID = M.MEMBER_ID
    ) AS REVIEW_COUNT
FROM MEMBER_PROFILE M;

⇒ select 절의 서브쿼리가 REVIEW_COUNT라는 하나의 컬럼으로 사용되고 있다.

⚠️select 절의 서브쿼리는 데이터의 양이 많을수록 실행속도가 느려 거의 사용되지 않는 명령어이다.

 

2️⃣ FROM 절 서브쿼리

⇒ 서브쿼리의 결과를 하나의 테이블처럼 사용

# rest_review 테이블에서 review_score=5인 member_id 출력
SELECT 
    r_cnt.MEMBER_ID,
    r_cnt.review_score
FROM (
        SELECT *
        FROM REST_REVIEW 
        WHERE review_score=5
    ) AS r_cnt
order by r_cnt.review_score;

⇒ from 절의 서브쿼리문을 r_cnt로 지정한 뒤, 메인 쿼리에서 이를 테이블처럼 참조하여 사용하고 있다.

⚠️ from 절에서 서브쿼리를 사용하기 위해서는 alias를 꼭 지정해야 한다. → 그래야 필터링된 테이블을 사용 가능

 

3️⃣ where 절 서브쿼리

⇒ 서브쿼리 결과를 if문과 같이 필터링을 위해 사용 가능

# rest_review 테이블에서, review_score가 5인 memeber 출력
SELECT 
    MEMBER_ID,
    MEMBER_NAME
FROM MEMBER_PROFILE
where member_id in (
                    select member_id 
                    from rest_review 
                    where review_score = 5)
order by member_name;

⇒ rest_review 테이블에서 review_score=5인 memeber_id만 필터링되어 메인쿼리의 where절에 사용되고 있다.

⚠️ where 절의 서브쿼리는 정말 많이 사용하니 잘 알아두자!

 

4️⃣ 기타 서브쿼리 참고

https://easyitwanner.tistory.com/274

[SQL] MySQL 서브 쿼리(SUBQUERY)란?

 

 

---

 

내가 처음에 작성한 답안(틀린 코드)

SELECT I.ID, N.FISH_NAME,I.LENGTH
FROM FISH_NAME_INFO AS N
INNER JOIN FISH_INFO AS I
ON I.FISH_TYPE=N.FISH_TYPE
GROUP BY N.FISH_TYPE
HAVING I.LENGTH = (SELECT MAX(LENGTH)
                   FROM FISH_INFO
                   ) AS LENGTH 
ORDER BY I.ID ASC;

 

위 코드의 문제점

1. GROUP BY절 사용 문제
→ GROUP BY 절에서 FISH_TYPE만을 그룹화하고 있지만, SELECT 절에서는 그룹화 되지 않은 열은 집계함수와 함께 사용되지 않는 한 SELECT 절에 포함될 수 없음.

2. HAVING절과 서브쿼리
→ 해당 서브쿼리는 FISH_INFO 테이블 전체에서 MAX를 반환함. 따라서 앞의 GROUP BY 절의 의미와 상충될 수 있음.

3. AS LENGTH 구문의 잘못된 위치
→ SQL에서는 서브쿼리 내에서 별칭을 지정할 수 없음.

 

 

문제 해결 코드 (정답)

SELECT I.ID, N.FISH_NAME, I.LENGTH
FROM FISH_NAME_INFO AS N
INNER JOIN FISH_INFO AS I
ON I.FISH_TYPE=N.FISH_TYPE
WHERE I.LENGTH = (SELECT MAX(N2.LENGTH)
                   FROM FISH_INFO AS N2
                   WHERE N2.FISH_TYPE = I.FISH_TYPE) 
ORDER BY I.ID ASC;