서브쿼리를 사용해보긴 했지만, 거의 WHERE문에서 사용했었다. SELECT문과 FROM문은 봐도 헷갈리는 부분이 있어서 이참에 서브쿼리에 대해 종합적으로 정리해보려고 한다.
서브쿼리란?
서브쿼리는 말그대로 메인쿼리에서 서브로 사용하는 또 다른 쿼리문이다. 메인쿼리에 종속적이며 서브쿼리가 사용되는 목적은 메인쿼리에서 최종적으로 필요한 데이터를 조회하기 위한 수단으로 사용된다.
서브쿼리는 더 복잡한 조건을 만족하는 검색이나 연산을 할 때 유용하므로 자주쓰이고 있다.
서브쿼리의 종류
중첩 서브쿼리(Nested Subquery)
WHERE절에서 사용하는 쿼리로, 서브쿼리 연산자를 사용하여 메인쿼리와 서브쿼리의 데이터를 비교하는 방식이다.
서브쿼리 연산자는 크게 비교 연산자와 EXISTS 연산자로 나눌 수 있다.
1. 비교연산자
비교 연산자는 서브쿼리의 결과값을 사용하여 메인쿼리의 조건식을 만족하는 데이터를 조회한다.
비교 연산자에는 대소비교 연산자(>, <, >=, <=), 등호연산자(=, <>) 등이 있다.
비교연산자를 활용하여 서브쿼리를 작성할 때는 서브쿼리로 조회되는 결과가 반드시 1건 이하여야 한다. == 단일행 서브쿼리
예시)
주문가격이 만원 이상인 고객들의 정보를 조회해보자
서브쿼리 없이 쿼리를 짠다면 밑의 코드처럼 될 것이다.
SELECT id
FROM 주문테이블
WHERE 주문가격 >= 10000;
SELECT id, 이름, 이메일
FROM 고객테이블
WHERE id in (위에서 실행한 쿼리의 결과값)이렇게 두번에 걸쳐서 먼저 조건을 충족하는 고객id를 조회하는 쿼리를 작성 한 후에, 새로운 쿼리로 위의 쿼리에서 받은 조회결과를 조건으로 만족하는 름, 이메일을 다시 조회한다.
쿼리를 두번에 걸쳐서 짜줘야 해서 복잡하다.
서브쿼리를 사용하면 위의 두 개의 쿼리를 하나의 쿼리로 사용이 가능하다.
SELECT id, 이름, 이메일
FROM 고객테이블
WHERE id IN (
SELECT 고객id
FROM 주문테이블
WHERE 주문가격 >= 10000
);위 코드는 서브쿼리를 활용하여 처음에 따로 각각 작성했던 코드를 합친거랑 똑같은 결과가 조회된다.
메인쿼리의 조회 조건인 where절에서 ( ) 괄호안에 조건으로 사용할 데이터를 조회하는 서브쿼리를 작성해주면 된다.
2. EXISTS 연산자
EXISTS 연산자는 서브쿼리의 결과값이 존재 하는지 여부를 확인하여 메인쿼리의 조건식을 만족하는 데이터를 조회한다. 만약 결과값이 존재한다면 true를 반환하고, 존재하지 않으면 false를 반환한다.
EXISTS 연산자는 NOT EXISTS 연산자와 함께 사용하며, 서브쿼리의 결과값이 존재하지 않는 데이터를 조회할 수도 있다.
예시)
고객 테이블에서 존재하는 이메일을 사용하는 주문이 있는지 확인하는 쿼리
SELECT *
FROM 고객테이블
WHERE EXISTS (
SELECT *
FROM 주문테이블
WHERE 고객테이블.id = 주문테이블.고객id
AND 고객테이블.이메일 = 주문테이블.이메일
);
위의 쿼리를 실행하면, 먼저 where절에 있는 exists 연산자로 평가될 서브쿼리가 먼저 실행된다.
만약 서브쿼리의 안에서 조회되는 행이 있다면(고객의 아이디와 일치하는 이메일을 사용하는 주문건수가 있다면), 즉 조회한 결과가 true 라면 해당 행을 모두 반환한다.
이 때 반환되는 행들은 여러행이 될 수 있다. == 다중 서브쿼리
만약 exists를 not exists로 바꾼다면 exists와 반대로 주문내역이 없는 고객정보가 담긴 행만 반환하게 된다.
인라인 뷰(Inline View)
FROM절에서 사용하는 서브쿼리.
FROM절에는 조회할 테이블을 작성하는데, FROM절에서 서브쿼리를 사용한다면 서브쿼리에 의해 선택된 결과집합은 하나의 테이블로써 사용되므로 반드시 이름(별칭)이 필요하다.
예시)
주문을 한 고객중에서 가장 많은 금액을 결제한 정보를 조회
select main.*
from (
select mc.*, sum(order_price) as 총주문금액
from my_order o
join my_customer mc on o.cu_id = mc.cu_id
group by mc.cu_id
order by 총주문금액 desc
limit 1
) as main;
from절에 오는 서브쿼리에서는 조건을 만족하는 고객의 정보를 조회하는 쿼리문에 들어간다. 조회된 결과셋은 테이블로써 사용되므로 main 이라는 별칭이 들어간다.
메인쿼리에서 조회되는 * 는 모든 정보를 의미하는데, from절에서 조회되는 컬럼들에 한해서 조회가 가능하다.
근데 생각해보니, 저 예시는 from절 서브쿼리를 사용하지 않고 join으로도 조회가 가능한 것 같아서 쿼리를 짜보았다.
select m.*, sum(o.order_price) 총주문금액 from my_customer as m
join my_order o on m.cu_id = o.cu_id
group by m.cu_id
order by 총주문금액 desc
limit 1;from절에 서브쿼리를 작성하지 않고 고객테이블과, 주문테이블을 조인하여 결과를 돌려봐도 결과는 똑같다.
동일한 결과를 반환한다면 인라인뷰와 join을 사용하는 방식과의 차이점은 뭘까?
FROM절 서브쿼리
FROM절에서 서브쿼리를 사용하는 방식은 서브쿼리 결과를 새로운 가상 테이블 만들어서 사용하는 방식이다.
따라서 서브쿼리가 실행될 때마다 해당 서브쿼리를 계산하고, 이를 메모리에 저장한 후에 다시 이를 이용해서 새로운 가상테이블을 만들어서 사용한다.
1. 서브쿼리 계산
2. 결과값 메모리에 저장
3. 새로운 가상테이블을 만들어서 사용
이 방식은 간단하지만 서브쿼리의 결과가 크거나 복잡할 경우에는 성능 이슈가 발생할 수 있다.
JOIN
JOIN을 사용하는 방식은 두개 이상의 테이블을 합쳐서 결과를 가져오는 방식이다.
이 방식은 조인할 테이블이 많을수록 더 복잡해질 수 있지만, FROM절에 서브쿼리를 사용하는 것 보다 성능면에서 더 좋다.
결론
따라서, 일반적으로 데이터 양이 많거나 복잡한 쿼리의 경우에는 JOIN 방식을 사용하는 것이 더 좋다.
스칼라 서브쿼리(Scalar Subquery)
SELECT절에서 사용하는 서브쿼리다.
스칼라는 하나의 수치만으로 완전히 표시되는 값 이라는 단어의 뜻 그대로 하나의 행만 반환하고, 일치하는 값이 없다면 Null을 반환한다.
서브쿼리로 조회된 결과를 조회할 컬럼으로 사용한다.
예시)
가장 많이 주문한 고객의 이름과, 주문횟수 조회
SELECT
이름,
(SELECT COUNT(*) FROM 주문테이블 WHERE 고객id = 고객테이블.id) AS 주문횟수
FROM
고객테이블
ORDER BY
주문횟수 DESC
LIMIT 1위의 쿼리에서는 count함수로 주문횟수를 같이 조회하기 위해 select 절에 서브쿼리를 사용한 경우다.
다음은 스칼라 서브쿼리를 사용하지 않고 Join을 이용하여 같은 결과값을 조회하는 쿼리다.
select cu_name, count(*) 주문횟수 from my_order o
join my_customer mc on o.cu_id = mc.cu_id
group by mc.cu_id
order by 주문횟수 desc limit 1;두 쿼리의 결과값은 동일하지만, 첫번째 쿼리는 스칼라 서브쿼리를 사용하여 각 고객의 주문횟수를 구하고, 그 결과를 각각의 행에 추가한다.
두번째 쿼리는 join을 사용하여 고객별 주문 수를 구한다.
조회하려는 데이터의 규모가 작다면 스칼라 서브쿼리나 join 중 어느것을 사용해도 큰 상관이 없지만, 대부분의 경우 더 효율적이고 빠른 join을 사용하는것이 더 권장된다.
