서브 쿼리의 종류에는 무엇이 있을까?
서브 쿼리는 다양한 비즈니스 상황에 사용됩니다. SQL의 서브 쿼리의 종류에는 어떤 것들이 있을까요? 그리고 그것들을 어떻게 효율적으로 사용할 수 있을까요? 이번 글에서는 서브 쿼리 종류를 살펴보고 그것들이 유용하게 쓰일 수 있는 상황에 대해 살펴보고자 합니다.
서브 쿼리는 무엇인가요?
서브 쿼리는 쿼리 안에 또 다른 쿼리가 들어간 것을 의미합니다. 상황에 따라 메인 쿼리의 WHERE, FROM, SELECT 절에 새로운 쿼리를 넣고 싶을 때가 생긴답니다.
새로운 개념을 이해할 때는 예시를 보면서 이해하는 게 가장 쉽죠. 이제 예시를 가지고 살펴보도록 합시다. 우리가 몇 개의 미술관을 운영한다고 가정해 봅시다. 그리고 우리의 데이터베이스 내에는 아래와 같은 테이블이 존재하고요.
첫 번째 테이블 : galleries
| id | city |
| 1 | London |
| 2 | New York |
| 3 | Munich |
두 번째 테이블 : paintings
| id | name | gallery_id | price |
| 1 | Patterns | 3 | 5000 |
| 2 | Ringer | 1 | 4500 |
| 3 | Gift | 1 | 3200 |
| 4 | Violin Lessons | 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 | gallery_id |
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
가장 직관적인 서브 쿼리의 예시 중 하나는 바로 결과를 필터링하고자 WHERE 절에 서브 쿼리를 포함하는 것입니다. 예를 들어, 평균 에이전시 비용보다 더 많은 비용을 받는 에이전트의 정보만 살펴보고 싶다면 아래와 같은 쿼리문을 작성할 수 있습니다.
SELECT *
FROM sales_agents
WHERE agency_fee
(SELECT AVG(agency_fee)
FROM sales_agents);
WHERE 절에 내에 있는 쿼리는 에이전시 비용의 평균을 계산해 1개의 단일 값(2728)을 반환합니다. 이 값을 가지고 메인 쿼리의 결과를 필터링하여 에이전시 평균 비용보다 더 많은 비용을 받는 에이전트의 정보를 반환해 내는 것이죠.
| id | last_name | first_name | gallery_id | agency__fee |
| 2 | White | Kate | 3 | 3120 |
| 4 | Smith | Helen | 1 | 4500 |
서브 쿼리는 단일 값을 반환할 수도 있고 여러 행과 열이 담긴 테이블을 반환할 수도 있습니다. 서브 쿼리에는 메인 쿼리의 결과를 필터링하고자 실행되는 내부 쿼리를 일컫는 중첩 쿼리(nested query)도 있고, 내부 쿼리가 메인 쿼리의 열을 빌려와 실행되는 상관관계가 있는 서브 쿼리(correlated)도 있습니다.
단일 수치를 반환하는 스칼라 서브 쿼리
서브 쿼리가 단일 값을 반환하거나 정확히 1개의 행과 1개의 열을 반환할 경우 우리는 이걸 스칼라 서브 쿼리라고 말합니다. 이 서브 쿼리는 메인 쿼리의 결과를 필터링하고자 WHERE 절에 자주 사용되고는 합니다. 우리가 앞의 예시에서 살펴본 서브 쿼리는 평균 에이전시 비용이라는 단일 값을 반환했기 때문에 스칼라 서브 쿼리라고 볼 수 있습니다.
또한, 스칼라 서브 쿼리는 메인 쿼리의 SELECT 문에도 사용할 수 있습니다. 예를 들어, 각각의 페인팅 금액 옆에 전체 페인팅의 평균 금액을 함께 보고 싶다고 가정해봅시다.
SELECT
name AS painting,
price,
(SELECT
AVG(price)
FROM paintings) AS avg_price
FROM paintings;
위 쿼리 내 서브 쿼리는 5840이라는 단일 값을 반환하고, avg_price라는 새로운 열에 값 5840 입력된 행을 반환합니다.
| painting | price | avg_price |
| Patterns | 5000 | 5840 |
| Ringer | 4500 | 5840 |
| Gift | 3200 | 5840 |
| Violin Lessons | 6700 | 5840 |
| Curiosity | 9800 | 5840 |
여기서 또 살펴볼 점은 이 예시에서 사용된 서브 쿼리(다른 말로 내부 쿼리)가 메인 쿼리(다른 말로 외부 쿼리)와는 별개로 독립적이라는 사실입니다. 서브 쿼리만 가지고도 쿼리가 실행이 되고, 그 자체로도 유의미한 수치를 얻어낼 수 있으니까요. 에이전시의 평균 비용을 알아낸 것처럼 말입니다.
여러 행을 반환하는 서브 쿼리
만약 서브 쿼리가 하나보다 더 많은 행을 반환한다면, 그 서브 쿼리는 여러 행을 반환하는 서브 쿼리(multiple-row subquery)라고 말합니다. 이 서브 쿼리의 종류는 2개로 나눠 볼 수 있는데요. 첫 번째. 여러 행이 존재하는 1개의 열을 반환하는 서브 쿼리. 두 번째, 여러 행이 존재하는 다수의 열을 반환하는 서브 쿼리입니다.
여러 행이 존재하는 1개의 열을 반환하는 서브 쿼리는 주로 WHERE 절에 사용되어 메인 쿼리의 결과를 필터링하는 데에 사용됩니다. 이 경우에는 보통 IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS와 같은 연산자와 함께 사용해 서브 쿼리를 통해 반환된 여러 개의 값을 특정 값과 비교할 수 있게 합니다.
예를 들어, 매니저가 아닌 에이전트들의 평균 에이전시 비용을 알고 싶다고 합시다. 해당 비용을 알려면 아래와 같은 서브 쿼리문을 작성할 수 있습니다.
SELECT
AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN (SELECT id
FROM managers);
우선 내부 쿼리가 실행되면서 매니저 아이디를 전부 반환할 것입니다. 그리고 메인 쿼리, 즉 외부 쿼리가 해당 매니저 아이디를 제외한 에이전트만 반환한 후 그들의 평균 에이전시 비용을 계산할 것입니다. 결국 외부 쿼리는 매니저가 아닌 에이전트들의 평균 비용인 1885이라는 단일한 값을 반환합니다.
상관관계가 있는 서브 쿼리
서브 쿼리 중에는 내부 쿼리가 외부 쿼리에서 얻은 정보에 의지해 실행되는 쿼리도 있습니다. 이걸 바로 상관관계가 있는 서브 쿼리(correlated subquery)라고 말합니다. 메인 쿼리와 내부 쿼리가 서로 상호 의지하는 탓에 이해하기 좀 어려울 수도 있습니다. 예시를 통해 살펴보도록 하죠. 상관관계가 있는 서브 쿼리는 주로 SELECT, WHERE, FROM 문에 사용됩니다.
만약 각 미술관마다 보유하고 있는 페인팅의 개수를 계산하고 싶다면 아래와 같은 쿼리를 작성할 수 있습니다. 상호관계가 있는 서브 쿼리가 SELECT 문에 사용된 점을 잘 살펴보세요.
SELECT
city,
(SELECT count(*)
FROM paintings AS p
WHERE g.id = p.gallery_id) AS total_paintings
FROM galleries AS g;
여기서의 서브 쿼리는 각 미술관이 보유하고 있는 페인팅의 개수, 즉 단일 값을 반환합니다. galleries 테이블 내 미술관 id가 총 3개 존재하므로 각 미술관 id를 가지고 paintings 테이블 내에 동일한 id을 가진 id의 개수를 세는 것이기 때문에 이 내부 쿼리는 총 3번 실행됩니다. 이 정보를 가지고 가지고 외부 쿼리는 galleries 테이블 내 도시 데이터와 함께 보여주는 것이죠.
| city | total_paintings |
| London | 2 |
| New York | 2 |
| Munich | 1 |
이전에 살펴본 예시와는 다르게 이번 예시 속 내부 쿼리는 외부 쿼리에 의존합니다. 외부 쿼리의 테이블인 galleries 테이블 내 저장된 미술관 id를 정보를 가져와 내부 쿼리의 테이블인 paintings 내에 저장된 미술관 id(gallery_id)와 연결해주는 것입니다. 즉, 다른 서브 쿼리와는 달리 이너 쿼리가 독립적으로 실행될 수 없다는 점입니다. 쿼리를 실행해봤자 에러만 뜰 거예요.
상관관계가 있는 서브 쿼리의 경우 서브 쿼리 대신 JOIN을 사용해 동일한 결과를 도출할 수도 있습니다.
SELECT
g.city,
count(p.name) AS total_paintings
FROM galleries AS g
JOIN paintings AS p
ON g.id = p.gallery_id
GROUP BY g.city;
JOIN은 보통 서브 쿼리보다 더 빨리 실행됩니다. 하지만 여러분이 분석을 하다가 서브 쿼리를 사용하는 것이 좀 더 직관적이라면 서브 쿼리를 사용하셔도 괜찮아요.
마지막으로, 상관관계가 있는 서브 쿼리는 WHERE 문에도 쓰일 수 있답니다. 예를 들어, 에이전트가 받는 에이전시 비용이 그 에이전트가 소속된 미술관의 평균 에이전시 비용과 같거나 더 많은 경우를 살펴보고 싶다고 합시다. 원하는 결과를 얻기 위해선 아래와 같은 쿼리를 실행할 수 있습니다.
SELECT
last_name,
first_name,
agency_fee
FROM sales_agents AS sa1
WHERE sa1.agency_fee > = (SELECT avg(agency_fee)
FROM sales_agents AS sa2
WHERE sa2.gallery_id = sa1.gallery_id);
여기서 사용된 내부 쿼리는 내부 쿼리의 테이블인 sales_agents (sa2) 내 5명의 에이전트가 있기 때문에 에이전트 그들이 소속된 미술관의 평균 에이전시 비용을 반환할 것입니다. 즉, 5명의 에이전트에 따른 각각의 평균 에이전시 비용(단일 값)을 총 5번 반환하는 것입니다. 결론적으로 이 내부 쿼리는 총 5번 실행됩니다. 그리고 외부 쿼리는 WHERE 문에 제시된 조건과 만족하는 에이전트에 관한 정보만 반환할 것이고요. 이 경우는 에이전트가 소속된 미술관의 평균 에이전시 비용보다 더 많이 받거나 똑같이 받는 에이전트의 정보를 반환하겠지요.
| last_name | first_name | agency_fee |
| Brown | Denis | 2250 |
| White | Kate | 3120 |
| Smith | Helen | 4500 |
여기서 사용된 서브 쿼리는 상관관계가 있는 서브 쿼리이기 때문에 외부 쿼리와는 별개로 독립적으로 실행될 수 없습니다.
비록 다양한 서브 쿼리의 종류가 분석의 다양한 사례를 제시하긴 하지만, 때로는 서브 쿼리 대신 Common Table Expressions을 사용하는 것이 용이할 때도 있습니다. CTEs에 대해 더 알아보고 싶다면, 서브 쿼리와 CTEs를 비교하는 글을 읽어보세요.
지금까지 서브 쿼리의 종류에 대해 살펴보았습니다. 이제는 여러분이 어떤 분석을 하는지에 따라 스칼라 서브 쿼리를 사용할 수도, 여러 행을 반환하는 서브 쿼리를 사용할 수도, 상관관계가 있는 서브 쿼리를 사용할 수도 있을 것입니다.