[SQL] 서브 쿼리 (SubQuery) : (2) 중첩 서브 쿼리 (WHERE절에 작성)

오늘은 서브 쿼리 중에서도 가장 많이 사용되는 "중첩 서브 쿼리"에 대해 알아보려고 한다.

서브 쿼리의 전반적인 개념 설명은 아래 링크를 참고하길 바란다.

[SQL] 서브 쿼리 (SubQuery) : (1) 기본 개념과 주요 예시

 

1. 중첩 서브 쿼리(Nested SubQuery)

• WHERE 절에 작성하는 서브 쿼리
• 메인 쿼리의 조건을 결정하기 위해 서브 쿼리 결과를 이용
• 반환 결과 형태에 따라 "단일 행 / 다중 행 / 다중 열"로 나뉜다.

 

2. 중첩 서브 쿼리 - 단일 행

• 서브 쿼리 결과가 단일 행을 반환한다.
• 단일 행 비교 연산자 : '=', '<', '>', '<=', '>='

 

3. 중첩 서브 쿼리 - 다중 행

• 서브 쿼리 결과가 다중 행을 반환한다.
• 다중 행 비교 연산자 : in, any, all

 

4. 중첩 서브 쿼리 - 다중 열

• 서브 쿼리 결과가 두 개 이상의 칼럼을 반환한다.
• PK가 복합키(두 개 이상의 칼럼으로 구성된 기본키)이거나, 여러 칼럼의 값을 한꺼번에 비교해야 할 경우 사용한다.
• 행 생성자를 이용하여 다중 열 서브 쿼리를 비교한다.

'행 생성자'(Row Constructor)란?
여러 컬럼 값을 하나의 행처럼 묶어, 다중 컬럼 조합을 정확하게 비교하기 위해 사용하는 SQL 문법이다.
대표적으로 다중 컬럼 IN 연산을 하기 위해 컬럼명을 ( col1, col2 ) 처럼 묶어둔 조합 자체가 행 생성자이다.

 

예시와 함께 살펴보자

 

예시 : FISH_INFO

다음과 같은 컬럼으로 구성된 FISH_INFO 테이블이 있다고 하자. (프로그래머스의 FISH_INFO 테이블과 동일)

Column name	Type	Nullable	Description
ID	INTEGER	FALSE	잡은 물고기의 ID
FISH_TYPE	INTEGER	FALSE	물고기의 종류(숫자)
LENGTH	FLOAT	TRUE	잡은 물고기의 길이(cm)
TIME	DATE	FALSE	물고기를 잡은 날짜

잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH가 NULL 이며, LENGTH에 NULL 만 있는 경우는 없다.

1) 단일 행 중첩 서브 쿼리 예시 : 전체 물고기 중 평균 길이보다 긴 물고기만 조회

SELECT id, fish_type, length
FROM fish_info
WHERE length > (
    SELECT AVG(length)    -- 서브쿼리를 통해 평균 길이를 구함
    FROM fish_info
);

2-1) 다중 행 중첩 서브 쿼리 中 IN 사용 예시 : 길이가 10 이상인 물고기 종류가 있는 물고기 조회

SELECT id, fish_type, length
FROM fish_info
WHERE fish_type IN (  -- 물고기 종류 중 서브 쿼리 결과에 있는 물고기만 뽑기
    SELECT fish_type  -- 길이가 10 이상인 물고기 종류만 조회
    FROM fish_info
    WHERE length >= 10
);

2-2) 다중 행 중첩 서브 쿼리 中 ANY 사용 예시 : 가장 짧은 물고기보다 길이가 긴 물고기 조회

SELECT id, fish_type, length
FROM fish_info
WHERE length > ANY (  -- 서브쿼리 결과의 최소값보다 크면 됨
	SELECT length
    FROM fish_info
);

MIN을 활용한 아래 코드도 동일한 결과가 나온다.

SELECT id, fish_type, length
FROM fish_info
WHERE length > (  -- 서브쿼리 결과의 최댓값보다 크면 됨
	SELECT MIN(length)
    FROM fish_info
);​

2-3) 다중 행 중첩 서브 쿼리 中 ALL 사용 예시 : fish_type = 0인 모든 물고기보다 길이가 긴 물고기 조회

SELECT id, fish_type, length
FROM fish_info
WHERE length > ALL (
	SELECT length
    FROM fish_info
    WHERE fish_type = 0
);

MAX를 활용한 아래 코드도 동일한 결과가 나온다.

SELECT id, fish_type, length
FROM fish_info
WHERE length > (
	SELECT MAX(length)
    FROM fish_info
    WHERE fish_type = 0
);​

3) 다중 열 중첩 서브 쿼리 : (id, fish_type) 조합 기준으로 길이가 10 이상인 물고기만 조회

SELECT id, fish_type, length
FROM fish_info
WHERE (id, fish_type) IN (
	SELECT id, fish_type
    FROM fish_info
    WHERE length >= 10
);

 

 

---

Reference

https://learn.microsoft.com/ko-kr/dotnet/framework/data/adonet/ef/language-reference/constructing-types-entity-sql

https://www.youtube.com/watch?v=hKXNg-H1YZk&t=30s

https://java119.tistory.com/49?category=809222

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%A0%95%EB%A6%AC?st_source=ai_mode#%EC%A4%91%EC%B2%A9_%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC_-_%EB%B3%B5%EC%88%98%EB%8B%A4%EC%A4%91_%ED%96%89