[MySQL] 서브쿼리(Subquery) 이용하기

오늘은 SQL 쿼리에서 자주 사용되는 서브쿼리(Subquery)에 대해 배웠습니다.
이번 글에서는 서브쿼리의 정의, 위치, 주요 연산자 등에 대해 적어보겠습니다.

 

[MySQL] 테이블 연결하기 (JOIN) - INNER / OUTER / LEFT / RIGHT / CROSS / SELF

이 글에서 사용했던 테이블들을 다시 이용했습니다.

그럼

시작!

 

1. 서브쿼리란?

: 메인 쿼리 안에서 실행되는 중첩 쿼리

 

예시 - 가장 높은 strength를 가진 영웅을 찾아냅니다.

SELECT name FROM myhero 
WHERE id = (
    SELECT id FROM ability
    WHERE strength = (SELECT MAX(strength) FROM ability)
);

똑같은 내용이 다시 나올 예정이니  넘어갑시다.

 

2. 서브쿼리의 위치

(1) SELECT 절에서의 서브쿼리

• SELECT절의 서브 쿼리는 결과가 하나의 값이어야 함

SELECT name, 
       (SELECT MAX(strength) FROM ability WHERE myhero.id = ability.id) max_strength 
FROM myhero;

- 각 히어로의 strength 값 중에서 가장 높은 값을 조회하여 max_strength 열로 반환

 

(2) FROM 절에서의 서브쿼리

• 인라인 뷰 서브쿼리
• FROM절의 서브 쿼리는 결과가 하나의 테이블이여야 함
• 서브 쿼리로 만든 테이블은 별명을 가져야 함

SELECT name, class_rank FROM (
	SELECT name, rank() OVER(ORDER BY class DESC) class_rank FROM myhero) t
WHERE class_rank = 3;

- class 순위가 3위인 행의 이름과 순위 가져오기

 

(3) WHERE 절에서의 서브쿼리

• 중첩 서브쿼리
• WHERE절의 서브 쿼리는 결과가 하나의 컬럼 이어야 함 (EXISTS 제외) 

SELECT name FROM myhero 
WHERE id IN (SELECT id FROM ability WHERE strength > 70);

- ability 테이블에서 strength가 70 이상인 영웅들의 ID를 서브쿼리로 찾고, 그 ID에 해당하는 영웅들의 이름을 메인 쿼리에서 조회

 

(4) HAVING 절에서의 서브쿼리

• 집계 함수와 함께 사용

SELECT tier, AVG(strength) avg_strength
FROM myhero M
JOIN ability A ON M.id = A.id
GROUP BY tier
HAVING AVG(strength) > (SELECT AVG(strength) FROM ability);

- 각 티어별 평균 strength가 전체 평균보다 높은 티어들만 조회

 

3. 서브쿼리와 함께 이용되는 연산자

(1)  =, >, <

: 하나의 값과 비교

SELECT name FROM myhero 
WHERE class = (SELECT MIN(class) FROM myhero);

 

(2) IN

: 여러 행을 조건으로 사용할 때

SELECT name 
FROM myhero 
WHERE class IN (SELECT class FROM myhero WHERE tier = '0+');

- tier가 '0+'인 영웅들의 class 값을 서브쿼리로 가져오고, 그 class 에 속한 영웅들의 이름을 조회

 

(3) ANY, ALL

: 서브쿼리에서 반환된 여러 행 중 특정 조건을 만족하는 경우를 비교

• ANY : 서브쿼리에서 반환된 값 중 하나라도 조건을 만족하면 참으로 간주
• ALL : 모든 값이 조건을 만족해야 참으로 간주

SELECT name FROM myhero 
WHERE class > ANY (SELECT class FROM myhero WHERE tier = '3');

SELECT name FROM myhero 
WHERE class > ALL (SELECT class FROM myhero WHERE tier = '3');

- tier가 '3'인 영웅들의 class 중 하나보다 큰 class 값을 가진 영웅을 조회

- tier가 '3'인 영웅들의 class의 값들 모두보다 큰 class 값을 가진다면 조회

 

(4) EXISTS

: 서브쿼리 결과가 있다면 참(True), 결과가 없으면 거짓(False)을 반환 > 특정 조건에 맞는 데이터가 존재하는지 확인할 때 사용

SELECT name FROM myhero 
WHERE EXISTS (SELECT * FROM ability WHERE ability.id = myhero.id AND strength > 90);

- ability 테이블에서 strength가 90 이상인 히어로가 존재하면, 해당 히어로의 이름을 출력

 

(*)

- ability.id = myhero.id가 상관 서브쿼리로 연결

- 서브쿼리가 메인 쿼리의 값을 참조하여 각 행마다 실행되기 때문에 id 값 비교 가능

 

4. Subquery와 JOIN 

"strength가 70 이상인 영웅들의 이름을 가져오세요"

저는 이런 걸 보면 JOIN이 먼저 떠오릅니다. 처음 이 내용 들을 때에는 서브쿼리를 어떻게 써야하는지 감이 안왔어요... 🥲

지금 정리를 하고 있다보니 이제 조금 알 것 같기도(?!) 합니다.

 

 

서브쿼리

: 간단한 필터링, 특정 값 조회에 적합

SELECT name FROM myhero 
WHERE id IN (SELECT id FROM ability WHERE strength >= 70);

 

JOIN

: 다중 테이블 결합 시 성능 우수, 복잡한 관계 처리에 적합

SELECT myhero.name FROM myhero 
JOIN ability ON myhero.id = ability.id 
WHERE ability.strength >= 70;

 

5. 연습 문제

1. 가장 높은 strength를 가진 히어로 찾기

SELECT name FROM myhero 
WHERE id = (
    SELECT id FROM ability
    WHERE strength = (SELECT MAX(strength) FROM ability)
);

 

2. 평균 이상의 intelligence와 strength를 모두 갖춘 히어로 찾기

SELECT name FROM myhero 
WHERE id IN (
    SELECT id FROM ability
    WHERE (intelligence > (SELECT AVG(intelligence) FROM ability))
        AND (strength > (SELECT AVG(strength) FROM ability))
 );