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

조인(JOIN) vs 서브쿼리(Sub Query)

조인과 서브쿼리는 때로 동일한 결과를 얻을 수 있다.

상황에 따라 조인을 사용하는 것이 훨씬 좋을 때도 있고, 반면에 서브 쿼리를 사용하는 것이 좋을 때도 있다.

 

서브 쿼리는 복잡한 SQL 쿼리문에 많이 사용된다.

보통은 메인 쿼리라고 부르는 외부 쿼리가 있고, 외부 쿼리 내에 다른 쿼리문, 즉 내부 쿼리가 있는 구조다.

[MYSQL] 📚 서브쿼리 정리

 

반면에, 조인은 여러 개의 쿼리를 필요로 하지 않는다.

조인의 역할은 2개 혹은 그 이상의 테이블을 연결하고, 연결한 테이블로부터 필요한 열을 조회할 수 있도록 한다. 

[MYSQL] 📚 JOIN 정리

 

서브 쿼리와 조인 모두 여러 개의 테이블로부터 데이터를 추출하기 위한 복잡한 쿼리문에 사용될 수는 있으나, 이 둘의 데이터를 추출하기 위한 접근 방식은 다르다.

분석에 따라 서브 쿼리와 조인 중 어느 것을 사용하든 상관이 없을 때도 있고, 둘 중 하나만이 유일한 해결 방법일 수도 있다.

서브쿼리는 가독성이 좋지만 성능이 조인에 비해 매우 좋지않다.
따라서 최신 MySQL은 사용자가 서브쿼리문을 사용하면 자체적으로 조인문으로 변환하여 실행시키도록 업데이트 되었다.
비록 내부적으로 변환해준다 하지만 꼭 필요한 경우를 제외하고는 너무 서브쿼리를 남용하지 않는 것을 권하는 편이다.

---

강의에 앞서, 아래의 2개 테이블을 예시로 사용할 예정이다.

 

[ 상품 목록 테이블 - product ]

id	name	cost	year	city
1	chair	245.00	2017	Chicago
2	armchair	500.00	2018	Chicago
3	desk	900.00	2019	Los Angeles
4	lamp	85.00	2017	Cleveland
5	bench	2000.00	2018	Seattle
6	stool	2500.00	2020	Austin
7	tv table	2000.00	2020	Austin

 

[ 판매 이력 테이블 - sale ]

id	product_id (외래키)	price	year	city
1	2	2000.00	2020	Chicago
2	2	590.00	2020	New York
3	2	790.00	2020	Cleveland
5	3	800.00	2019	Cleveland
6	4	100.00	2020	Detroit
7	5	2300.00	2019	Seattle
8	7	2000.00	2020	New York

---

서브 쿼리를 조인으로 대체할 수 있는 경우

 

1) 중첩 서브 쿼리 (단일 행)

중첩 서브 쿼리란, where 절에 서브쿼리를 사용하는 것을 의미한다.

만일 내부 쿼리가 단일한 값을 반환한다면 이는 조인으로도 충분히 구현할 수 있게 된다.

 

Q. 2,000 달러에 팔린 product의 이름과 가격을 알고 싶다고 가정해보자.

서브 쿼리를 이용해 아래와 같이 작성할 수 있다.

-- sale에서 2000원에 팔린 product_id와 projuct테이블의 id와 일치한 것을 가져온다.
SELECT name, cost
FROM product
WHERE id =
( 
  SELECT product_id
  FROM sale
  WHERE price = 2000
  AND product_id = product.id 
);

name	cost
armchair	500.00
tv table	2000.00

여기서 주의할 점은 아이디를 비교하는 과정에서 각 id 와 product_id 필드를 비교할 때마다 내부 쿼리가 실행된다는 점이다.

쿼리를 계속해서 반복해서 날리니, 성능이 낮아질수 밖에 없다.

 

이는 조인을 사용하여 똑같은 결과를 얻어낼 수 있다.

[ INNER JOIN으로 변환 ]

SELECT p.name, p.cost
FROM product AS p
JOIN sale AS s -- inner join
ON p.id = s.product_id
WHERE s.price = 2000;

 

2) 중첩 서브 쿼리 (복수행)

IN 연산자 안에 서브 쿼리가 있다는 것은 서브 쿼리가 여러 개의 값을 반환하여, id를 여러개와 비교한다는 뜻이다.

이 역시 해당 서브 쿼리를 조인으로 바꿔 쓸 수 있다.

 

Q. 예를 들어, 판매된 상품들의 이름과 가격을 알고 싶다고 가정해보자.

SELECT name, cost
FROM product
WHERE id IN ( SELECT product_id FROM sale );
-- 서브 쿼리가 반환한 product_id 목록은 sale 테이블에서 가져왔기 때문에, 이는 오직 판매된 상품을 의미.

name	cost
armchair	500.00
lamp	85.00
bench	2000.00
desk	900.00

product 테이블에는 상품의 종류가 7개이지만, 결과를 보니 팔린 상품의 종류는 4개뿐인 것을 확인할 수 있다.

 

[ INNER JOIN으로 변환 ]

# 1:1 inner join을 하고 중복되는 레코드는 제거
SELECT distinct p.name, p.cost
FROM product AS p
JOIN sale AS s
ON p.id = s.product_id;

 

3) 중첩 서브 쿼리 (복수행) - NOT IN 연산자

SELECT name, cost
FROM product
WHERE id NOT IN ( SELECT product_id FROM sale );
-- sale 즉 팔리지 않는 상품들만 출력

name	cost
chair	245.00
stool	2500.00

 

[ Exclusive JOIN으로 변환 ]

A가 product / B가 sale 이라 보면 된다

-- sale 테이블과 조인해도 sale 레코드값을 가지고 있지 않는 product 컬럼 풀력
SELECT DISTINCT p.name, p.cost
FROM product AS p
LEFT JOIN sale AS s
ON p.id = s.product_id
WHERE s.product_id IS NULL;

LEFT JOIN을 통해 상품의 판매 유무와는 상관없이 모든 상품들이 조인한 테이블에 나타나게 된다.

그런 후, WHERE 절에서 product_id 가 존재하지 않는 경우(NULL)를 찾음으로써 서브 쿼리의 아이디가 외부 쿼리 테이블에 존재하지 않는 경우를 재현할 수 있다.

 

4) EXISTS / NOT EXISTS 서브 쿼리

EXISTS와 NOT EXISTS 연산자 안에 있는 서브 쿼리도 조인으로 대체하여 쓰기에 쉽다.

EXISTS 연산자란?
exists는 서브쿼리 전용 연산자로서, 서브쿼리가 반환하는 결과값이 있는지를 조사한다.
서브쿼리는 부모쿼리의 컬럼값을 가져와 사용할 수 있는데 (마치 java의 자식클래스의 상속개념 같이), 서브쿼리 내에서 비교하고, 단지 반환된 행이 있는지 없는지만 보고 값이 있으면 참 없으면 거짓을 반환한다.

 

Q. 2020년도에 팔리지 않은(NOT EXISTS) 상품들의 정보를 조회

SELECT name, cost, city
FROM product as p
WHERE NOT EXISTS
( 
  SELECT id 
  FROM sale 
  WHERE year = 2020 AND product_id = p.id -- 부모쿼리 p.id 와 서브쿼리 product_id 끼리 비교하고 반환값이 있는지 없는지
);

name	cost	city
chair	245.00	Chicago
desk	900.00	Los Angeles
bench	2000.00	Seattle
stool	2500.00	Austin

 

[ LEFT JOIN으로 변환 ]

SELECT p.name, p.cost, p.city
FROM product AS p
LEFT JOIN sale AS s
ON p.id = s.product_id
WHERE s.year != 2020 OR s.year IS NULL; 
-- 2020년도에 안팔리거나, left join 결과 sale테이블에 없는 상품일경우 (null인경우)

EXISTS는 상황에 따라 조인(JOIN) 보다 수행시간이 더 빠를 수도 있다.
EXISTS의 경우는 inner query를 만족하는 레코드를 처음 만나는 순간 EXISTS가 true이므로 inner query를 더 이상 평가하지 않는다.
그래서 일반적으로 EXISTS보다 JOIN 속도가 빠르다고 하지만, 중복된 값이 많이 나올 경우에는 EXISTS가 더 빠르다.

---

서브 쿼리를 조인으로 대체할 수 없는 경우

 

1) GROUP BY 를 사용한 서브 쿼리가 FROM 절에 있을 때

집계된 값들을 구하고자 GROUP BY 를 사용한 서브 쿼리가 FROM 절에 들어 있을 때 (스칼라 서브쿼리) 조인으로 변환이 불가능하다.

SELECT city, sum_price
FROM
( 
  SELECT city, SUM(price) AS sum_price 
  FROM sale
  GROUP BY city -- group by 를 쓴 스칼라 서브쿼리
) AS s
WHERE sum_price < 2100;

city	sum_price
Chicago	2000.00
Detroit	100.00
Cleveland	1590.00

 

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

서브 쿼리가 집계된 하나의 값을 반환하고 그 값을 WHERE 절에서 외부 쿼리의 값과 비교할 때 변환이 불가능하다.

SELECT name
FROM product
WHERE cost < ( SELECT AVG(price) FROM sale ); -- 평균값을 반환하고 비교

name

chair

armchair

desk

lamp

 

3) 서브 쿼리가 ALL 연산자에 있을 때

서브쿼리를 IN 이나 EXISTS 연산자를 사용할 경우 변환이 가능했지만, 서브 쿼리가 ALL 연산자 안에 들어 있는 경우 변환이 불가능하다.

SELECT name
FROM product
WHERE cost > ALL( SELECT price FROM sale )

name

stool