서브쿼리 vs 조인

서브쿼리와 조인에 중 무엇을 써야 할까?

 

서브쿼리의 종류 

서브쿼리는 쿼리 안에 쿼리가 들어간 것으로 바깥의 쿼리를 메인 쿼리 또는 외부 쿼리라고 한다. 안에 들어간 쿼리는 서브쿼리 또는 내부 쿼리라고 부른다. 서브쿼리는 메인 쿼리의 where, from, select 절 등에 쓰인다. 

 

서브쿼리의 종류에 대해 알아보자. 

먼저 서브쿼리가 메인 쿼리와 연관이 있는지 여부에 따라 분류할 수 있다. 

nested query

nested query는 메인 쿼리와 관련이 없는 서브쿼리다. 따라서 서브쿼리 자체로도 실행될 수 있다. 

SELECT *
FROM sales_agents
WHERE agency_fee
 (SELECT AVG(agency_fee)
  FROM sales_agents);

서브쿼리가 메인 쿼리와 관련 없이 독자적으로 실행될 수 있으므로 nested query다. 

correlated query

correlated query는 메인 쿼리와 관련이 있는 서브쿼리로 독자적으로 실행될 수 없다. 

SELECT
 city,
 (SELECT count(*)
  FROM paintings AS p
  WHERE g.id = p.gallery_id) AS total_paintings
FROM galleries AS g;

서브쿼리가 메인 쿼리의 g.id와 연관이 있다. 따라서 서브 쿼리 독자적으로 실행할 수 없는 correlated query이다. 

 

다른 분류 방법으로는 서브쿼리가 반환하는 결과에 따라 분류할 수도 있다. 

스칼라 서브쿼리 

서브쿼리가 단일 값을 반환하거나, 하나의 행+하나의 열을 반환하는 경우 스칼라 서브쿼리라 한다. where 또는 select 절에 주로 사용된다. 

SELECT
 name AS painting,
 price,
 (SELECT
    AVG(price)
   FROM paintings) AS avg_price
FROM paintings;

select 절에 스칼라 서브쿼리를 사용했다. 

 

multiple row subquery

서브쿼리가 2개 이상의 행을 반환하면 multiple row subquery이다. 

multiple row subquery도 여러 행+하나의 열인 경우와 여러 행+여러 열인 경우로 나뉜다. 

SELECT
 AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN (SELECT id FROM managers);

여러 행+하나의 열(id)의 경우이며, where절 에서 메인 쿼리의 결과를 필터링하는데 사용되고 있다. 

이처럼 여러 행+하나의 열 서브쿼리는 주로 where절에서 in, not in, any, all, exists, not exists와 같은 연산자와 함께 메인 쿼리의 결과를 필터링 하는데 사용된다. 

 

서브쿼리 vs 조인 

서브쿼리를 조인으로 바꿀 수 있는 경우

대부분의 경우 조인이 서브쿼리에 비해 가독성이 좋고 성능도 뛰어나다. 서브쿼리를 조인으로 대체하는 방법에 대해 알아보자. 

스칼라 서브쿼리 

select `name`, cost 
from product p
where p.id = ( 
select s.product_id 
from sale s 
where s.price = 2000.00
and s.product_id = p.id
);

서브쿼리에서 s.product_id라는 하나의 열 + s.product_id=p.id라는 조건에 해당하는 하나의 행만 반환하므로 스칼라 서브쿼리다. 이 서브쿼리는 스칼라 서브쿼리이면서 correlated subquerry이다. 왜냐하면 서브쿼리에서 외부 쿼리의 p.id를 사용하므로 외부 쿼리와 연관이 있다. 하나의 p.id 에 대해 서브쿼리가 한번 씩 실행되야 하므로 서브쿼리가 여러번 실행되어 성능이 저하된다. 

select `name`, cost 
from product p
join sale s 
on p.id = s.product_id
where s.price = 2000.00;

스칼라 서브쿼리를 조인으로 바꿔 성능이 향상되었다. 

select `name`, cost 
from product p
where p.id in ( 
select s.product_id 
from sale s 
where s.price = 2000.00);

사실 이렇게 multiple row subquery로 바꿔 써도 될 것 같다..  

IN 연산자 안의 서브쿼리

# 판매된 상품들의 이름과 가격 
select `name`, cost
from product 
where id in (
select product_id 
from sale);

위와 같이 in 연산자와 함께쓰인 서브쿼리를 join으로 바꿔보자.

# 판매된 상품들의 이름과 가격 
select distinct p.name, p.cost 
from product p 
join sale s
on p.id = s.product_id;

 inner join을 사용해 변환했다. 주의할 점은 distinct 를 사용해 해 중복을 제거한 점이다. 서브쿼리를 조인으로 변환할 때 distinct 를 사용해야 하는 경우가 많다.   

NOT IN 연산자 안의 서브쿼리

#판매되지 않은 상품의 이름과 가격
select `name`, cost
from product 
where id not in (
select product_id 
from sale);

not in  연산자와 함께 쓰인 서브쿼리를 조인으로 바꿔보자. 

#판매되지 않은 상품의 이름과 가격
select distinct p.`name`, p.cost 
from product p
left join sale s 
on p.id = s.product_id
where s.product_id is null;

 

이번에는 left join 을 사용했다. s.product_id = null 조건으로 sale 테이블에 없는 상품을 결과로 얻었다. 

 

EXISTS, NOT EXISTS 안의 서브쿼리 

# 2020년도에 팔리지 않은 상품들의 정보
SELECT 
    *
FROM
    product p
WHERE
    NOT EXISTS( SELECT 
            s.product_id
        FROM
            sale s
        WHERE
            s.year = 2020 AND p.id = s.product_id);

exists, not exists 는 주로 where 절에 서브쿼리와 쓰이며, 반환 결과 true, false 에 따라 메인 쿼리를 필터링하는데 쓰인다. 

 

# 2020년도에 팔리지 않은 상품들의 정보
SELECT 
    *
FROM
    product p
        LEFT JOIN
    sale s ON p.id = s.product_id
WHERE
    s.year != 2020 OR s.year IS NULL;

위와 같이 left join 을 사용해 조인으로 변경했다. 팔렸지만 2020년도가 아니거나 아예 팔리지 않아서 year 값이 null 인 데이터를 얻는다. 

서브쿼리를 조인으로 바꿀 수 없는 경우 

group by를 사용한 서브쿼리가 from절에 있을 때

select *
from (
select product_id, sum(price) as price_sum
from sale 
group by product_id
) as s
where s.price_sum <= 2000;

group by 를 사용한 서브쿼리가 from 에 있으면 join 으로 대체할 수 없다.

 

집계된 값을 반환하는 서브쿼리가 where 절에 있을 때 

select *
from product p1
where p1.cost >= (
select avg(p2.cost)
from product p2);

집계된 값 avg(p2.cost) 를 반환하는 서브쿼리가 where  절에 있을 때는 join으로 바꿀 수 없다. 

 

서브쿼리가 all 연산자에 있을 때 

select *
from product p
where p.cost > all (select price from sale);

서브쿼리가 all  연산자와 함께 쓰이는 경우 join으로 바꿀 수 없다. 

 

 

출처

https://kimsyoung.tistory.com/entry/%EC%84%9C%EB%B8%8C-%EC%BF%BC%EB%A6%AC%EC%9D%98-%EC%A2%85%EB%A5%98%EC%97%90%EB%8A%94-%EB%AC%B4%EC%97%87%EC%9D%B4-%EC%9E%88%EC%9D%84%EA%B9%8C

서브 쿼리의 종류에는 무엇이 있을까?

https://kimsyoung.tistory.com/entry/SUBQUERY-%EC%99%80-JOIN-%EC%9D%98-%EC%B0%A8%EC%9D%B4-%E4%B8%8A

SUBQUERY 와 JOIN 의 차이 (上)

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-JOIN%EA%B3%BC-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%B0%A8%EC%9D%B4-%EB%B0%8F-%EB%B3%80%ED%99%98-%F0%9F%92%AF-%EC%A0%95%EB%A6%AC#1_group_by_%EB%A5%BC_%EC%82%AC%EC%9A%A9%ED%95%9C_%EC%84%9C%EB%B8%8C_%EC%BF%BC%EB%A6%AC%EA%B0%80_from_%EC%A0%88%EC%97%90_%EC%9E%88%EC%9D%84_%EB%95%8C

[MYSQL] 📚 JOIN과 서브쿼리 차이 및 변환 💯 정리