[MYSQL] 서브 쿼리

서브 쿼리에 대해 알아보자
https://sqltest.net 해당 사이트에서 실제 테이블을 만들어 보면서 하면 도움이 된다.

서브 쿼리는 쿼리 안에 또 다른 쿼리가 들어간 것을 의미한다. 보통 메인 쿼리, 서브 쿼리로 칭한다.
상황에 따라 메인 쿼리에 SELECT, FROM, WHERE 절에 새로운 쿼리가 필요한 경우에 사용한다.

아래 예시로 한번 살펴보자

미술관에 GALLERIES, PAINTINGS, SALES_AGENTS, MANAGERS 라는 4개의 테이블이 있다.

GALLERIES

id	city
1	London
2	NewYork
3	Munich

PAINTINGS

id	name	gallery_id	price
1	Patterns	3	5000
2	Ringer	1	4500
3	Gift	1	3200
4	Violin	2	6700
5	Curiosity	2	9800

SALES_AGENTS

id	last_name	first_name	gallery_id	agency_fee
1	Brown	Denis	2	2250
2	White	Kate	3	3120
3	Black	Sarah	2	1640
4	Smith	Helen	1	4500
5	Stewart	Tom	3	2130

MANAGERS

id	gellery_id
1	2
2	3
3	1

가장 직관적인 WHERE 절에 서브 쿼리를 확인하기 위해 에이전시의 평균 비용보다 높은 비용을 받는 에이전트를 확인하는 쿼리를 작성해보자

SELECT
	*
FROM 
	SALES_AGENTS
WHERE
	agency_fee > (SELECT AVG(agency_fee) FROM SALES_AGENTS)

WHERE 절에 있는 서브 쿼리를 통해 에이전시 비용의 평균을 계산하고 해당 평균 보다 높은 에이전시 비용을 필터링 해주면 된다.

스칼라 서브 쿼리

서브 쿼리가 단일 값을 반환하거나 정확히 1개의 행과 1개의 열을 반환하면 스칼라 서브 쿼리라고 한다.
앞의 예시가 스칼라 서브 쿼리라고 볼 수 있다.

스칼라 서브 쿼리는 메인 쿼리의 SELECT 절에서도 사용할 수 있다.
예시로 각각의 페인팅 금액 옆에 전체 페인팅의 평균 금액을 함께 출력하는 쿼리를 작성해보자

SELECT
	name AS painting,
	price,
    (SELECT ROUND(AVG(price), 0) FROM PAINTINGS) AS avg_price
FROM
	PAINTINGS

여기서 확인할 수 있는 부분은, 서브 쿼리만으로도 쿼리가 실행 되고 에이전시의 평균 비용을 알아낸 것처럼 예시에서 사용된 서브 쿼리가 메인 쿼리와는 별개로 독립적이라는 것이다.

여러 행을 반환하는 서브 쿼리

여러 행을 반환하는 서브 쿼리는 2종류로 나눌 수 있다.

• 여러 행이 존재하는 1개의 열을 반환하는 서브 쿼리
• 여러 행이 존재하는 다수의 열을 반환하는 서브 쿼리

여러 행이 존재하는 1개의 열을 반환하는 서브 쿼리는 주로 WHERE 절에 사용되어 메인 쿼리의 결과를 필터링하는 데에 사용된다.

보통 IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS와 같은 연산자와 함께 사용해 서브 쿼리를 통해 반환된 여러 개의 값을 특정 값과 비교할 수 있다.

예를 들어, 매니저가 아닌 에이전트들의 평균 에이전시 비용을 알고 싶을 때, 아래와 같은 서브 쿼리를 통해 확인할 수 있다.

SELECT
    ROUND(AVG(agency_fee), 0) AS agency_fee
FROM
    SALES_AGENTS
WHERE 
    id NOT IN (
               SELECT id FROM MANAGERS
              )

해당 쿼리에서 WHERE 절에 사용된 서브 쿼리를 통해 출력된 매니저의 id를 제외한 id를 가진 에이전트가 반환되고 해당 에이전트의 평균 비용을 출력한다.

상관관계가 있는 서브 쿼리

내부 쿼리가 외부 쿼리에서 얻은 정보에 의해 실행되는 쿼리를 뜻한다.
상관관계가 있는 서브 쿼리는 주로 SELECT, WHERE, FROM 문에 사용된다.

말로만 보면 헷갈리니 예시를 통해 확인해보자

미술관마다 보유하고 있는 페인팅의 개수를 계산하고 싶다면 아래와 같은 쿼리를 작성할 수 있다. 상관관계가 있는 서브 쿼리가 SELECT 문에 사용된 점을 잘 확인해보자

SELECT
    id,
    city,
    (SELECT count(*) 
     FROM	PAINTINGS P 
     WHERE G.id = P.gallery_id
	) AS printing_total
FROM 
	GALLERIES G

여기서 서브쿼리는 미술관의 id와 페인팅 테이블의 갤러리 id가 일치한 페인팅의 개수를 반환하고, 이 정보를 가지고 외부 쿼리는 city 데이터와 함깨 보여준다.

앞선 예시와 달리 해당 내부 쿼리는 외부 쿼리인 갤러리 테이블의 id 정보를 가져와 쿼리를 실행한다. 앞선 예시와 달리 해당 내부 쿼리는 독립적으로 실행할 수 없다.

상관관계가 있는 서브 쿼리는 WHERE 문에도 쓰일 수 있다.

예를 들어, 에이전트가 받는 에이전시 비용이 그 에이전트가 소속된 미술관의 평균 에이전시 비용과 같거나 더 많은 경우를 확인하고자 하면 아래와 같은 쿼리를 통해 확인할 수 있다.

SELECT
    last_name,
    first_name,
    agency_fee
FROM 
    SALES_AGENTS S1
WHERE
    agency_fee >= (
                  SELECT
                    AVG(agency_fee)
                  FROM
                    SALES_AGENTS S2
                  WHERE
                    S1.gallery_id = S2.gallery_id
                  )

이런 식으로 외부 쿼리의 테이블을 참조하여 조회할 수 있다.