SUBQUERY 와 JOIN 의 차이 (上)

SQL 쿼리문을 작성하면서 마주할 고민 중 하나는 바로 서브 쿼리와 조인 중 어떤 문법을 사용하는 것이 좋을지 판단하는 것입니다. 상황에 따라 조인을 사용하는 것이 훨씬 좋을 때도 있고, 반면에 서브 쿼리를 사용하는 것이 좋을 때도 있습니다. 그래서 이번 글에서는 각 상황을 훑어보려고 합니다.

서브 쿼리는 복잡한 SQL 쿼리문에 많이 사용됩니다. 보통은 메인 쿼리라고 부르는 외부 쿼리가 있고, 외부 쿼리 내에 다른 쿼리문, 즉 내부 쿼리가 있는 구조입니다. 서브 쿼리의 구조는 간단할 수도 있고 조금 복잡할 수도 있습니다. 간단한 형태의 서브 쿼리는 내부 쿼리가 외부 쿼리의 열에 의존하지 않습니다. 내부 쿼리만으로도 충분히 쿼리가 실행 가능해야 합니다. 반면에 좀 더 복잡한 형태의 서브 쿼리는 내부 쿼리가 외부 쿼리의 열에 의존합니다. 즉, 내부 쿼리만으로 쿼리가 실행이 불가능하다는 말입니다. 서브 쿼리에 관해 더 자세한 내용은 서브 쿼리의 종류에는 무엇이 있을까? 를 읽어주세요.

반면에, 조인은 여러 개의 쿼리를 필요로 하지 않습니다. 조인의 역할은 2개 혹은 그 이상의 테이블을 연결하고, 연결한 테이블로부터 필요한 열을 조회할 수 있도록 합니다. 

서브 쿼리와 조인 모두 여러 개의 테이블로부터 데이터를 추출하기 위한 복잡한 쿼리문에 사용될 수는 있으나, 이 둘의 데이터를 추출하기 위한 접근 방식은 다릅니다. 여러분의 분석에 따라 서브 쿼리와 조인 중 어느 것을 사용하든 상관이 없을 때도 있고, 둘 중 하나만이 유일한 해결 방법일 수도 있습니다. 지금부터 다양한 사례를 살펴보도록 하겠습니다.

이번 글에서는 아래의 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

 

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

SQL이 아직 익숙하지 않으신 분들은 조인으로 쉽게 작성할 수 있는 쿼리문을 서브 쿼리로 작성하곤 합니다. 서브 쿼리가 조인에 비해 직관적이라서 이해하기 쉽기 때문에 실제로 많은 SQL 사용자들이 선호하기는 하지만, 때로는 조인이 훨씬 효과적이랍니다. 조인은 쿼리문이 복잡해지더라도 서브 쿼리에 비해 읽어내기 수월합니다. 그래서 가장 첫 번째로 서브 쿼리를 조인으로 대체할 수 있는 경우들을 살펴보고자 합니다. 

 

1) 스칼라 서브 쿼리

첫 번째 경우는 스칼라 서브 쿼리입니다. 스칼라 서브 쿼리는 내부 쿼리가 단일 값을 반환하거나 1개의 열과 1개의 행을 반환하는 경우를 말합니다. 즉, 내부 쿼리가 단일한 값을 반환한다면 이는 조인으로도 충분히 구현할 수 있다는 이야기입니다. 예시를 볼까요?

2,000 달러에 팔린 상품의 이름과 가격을 알고 싶다고 가정해봅시다. 서브 쿼리를 이용해 아래와 같이 작성할 수 있습니다.

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

외부 쿼리가 상품의 이름과 가격(cost)을 조회했습니다. 우리는 모든 상품에 관해 알고 싶은 것이 아니므로 WHERE 절을 사용하여 서브 쿼리를 통해 구한 상품 아이디만 조회될 수 있도록 하였습니다. 그럼 서브 쿼리, 즉 내부 쿼리를 살펴보도록 하죠. sale 테이블은 각 상품의 판매가(price)에 관한 정보를 담고 있습니다. 서브 쿼리는 제일 먼저 1) 판매가가 2,000 달러인 상품만 남겨둔 후 2) 외부 쿼리의 id와 내부 쿼리의 product_id를 비교하여 일치하는 상품의 아이디만 반환해줍니다. 여기서 주의할 점은 아이디를 비교하는 과정에서 각 아이디를 비교할 때마다 내부 쿼리가 실행됩니다. 그래서 매번 단일한 값이 반환되므로 스칼라 서브 쿼리라고 볼 수 있습니다. 또한, 이렇게 내부 쿼리가 실행되기 위해서 외부 쿼리에 의존하는 것을 상관관계가 있는 서브 쿼리라고 부릅니다. 결과는 2개의 상품이 2,000 달러에 판매되었습니다. 

하지만, 해당 쿼리문은 효율적이지 못합니다. 그 이유는 서브 쿼리 내에서 외부 쿼리의 id와 내부 쿼리의 product_id를 매칭 하는 과정에서 각 아이디마다 내부 쿼리가 실행되므로 결과적으로 전체 쿼리가 실행되는 시간이 길어질 수밖에 없습니다. 그렇다면, 이를 어떻게 수정하면 좋을까요?

조인을 사용하여 똑같은 결과를 얻어낼 수 있습니다. 이번에는 조인을 사용한 쿼리문입니다.

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

쿼리문을 보면, 조인을 통해 product 테이블과 sale 테이블을 연결했습니다. 두 테이블을 연결하기 위해 product 테이블의 아이디 열과 sale 테이블의 product_id 열을 서로 매칭 하였고요. 마지막 문장에 WHERE 절을 통해 판매가(price)가 2,000 달러인 상품만 조회될 수 있도록 필터링하였습니다. 

 

2) IN 연산자 안에 있는 서브 쿼리

IN 연산자 안에 서브 쿼리가 있다면 해당 서브 쿼리를 조인으로 바꿔 쓸 수 있습니다. 이 경우는 내부 쿼리, 즉, 서브 쿼리가 여러 개의 값을 반환합니다. 예를 들어, 판매된 상품들의 이름과 가격을 알고 싶다고 가정해봅시다. 원하는 결과를 얻기 위해 아래와 같은 쿼리문을 작성할 수 있습니다.

SELECT
 name,
 cost
FROM product
WHERE id IN ( SELECT product_id FROM sale );

외부 쿼리는 proudct 테이블에서 이름과 가격을 조회합니다. 그런 후 서브 쿼리에서 반환된 아이디들을 통해 결과값을 추가로 필터링하는 과정을 거칩니다. 참고로, 서브 쿼리가 반환한 아이디 목록은 sale 테이블에서 가져왔기 때문에, 이는 오직 판매된 상품의 아이디만을 의미합니다.

name	cost
armchair	500.00
lamp	85.00
bench	2000.00
desk	900.00

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

그렇다면 똑같은 결과를 얻기 위해 이번에는 조인을 사용해 보도록 하겠습니다. 

SELECT DISTINCT
 p.name,
 p.cost
FROM product AS p
JOIN sale AS s
   ON p.id = s.product_id; 

이는 꽤나 간단한 쿼리문입니다. product_id 로 2개의 테이블은 연결한 후 그 테이블에서 상품 이름과 가격을 조회하였습니다. 여기서 쓰인 조인의 종류는 이너 조인인데, 그 이유는 우리가 판매된 상품들의 정보만 보고 싶었기 때문입니다. 이너 조인을 사용함으로써 sale 테이블에 아이디가 없는 상품들은 결과 테이블에 조회되지 않습니다.

또한, 결과 중에서 혹시나 있을 중복값을 제거하고자 DISTINCT 키워드를 사용했습니다. 이는 IN 또는 NOT IN 연산자를 조인으로 바꿔 쓸 때 종종 거쳐야 하는 작업입니다. 

 

3) NOT IN 연산자 안에 있는 서브 쿼리

이는 바로 직전에 살펴본 IN 연산자 대신에 NOT IN 연산자를 사용했다는 차이밖에 없습니다. 이번에는 판매되지 않은 상품의 이름과 가격을 알아보고자 합니다. 아래는 NOT IN 연산자를 사용한 서브 쿼리문입니다. 한 번 보시죠. 

SELECT
 name,
 cost
FROM product
WHERE id NOT IN ( SELECT product_id FROM sale );

결과는 다음과 같습니다.

name	cost
chair	245.00
stool	2500.00

서브 쿼리가 sale 테이블에서 팔린 상품의 아이디의 목록을 반환했습니다. 그래서 그것들을 가지고 외부 쿼리의 상품 아이디와 비교하여 결과를 얻어낼 수 있었습니다. NOT IN 연산자이므로 서브 쿼리를 통해 얻어낸 아이디가 외부 쿼리 테이블에 존재하지 않는다면, 그 아이디의 상품명과 가격이 조회됩니다. 그렇다면, 해당 서브 쿼리를 어떻게 조인으로 바꿔 쓸 수 있을까요?

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;

이 쿼리는 상품 아이디로 product 테이블과 sale 테이블을 연결합니다. IN 연산자가 들어가 있기 때문에 서브 쿼리를 조인으로 바꿀 때 DISTINCT 키워드를 사용하여 혹시나 있을 중복값을 제거합니다. 

이번 쿼리문에서 주의하여 살펴볼 점은 NOT IN 연산자 안의 서브 쿼리를 새로 작성하는 과정에서 LEFT JOIN과 WHERE 절을 사용했다는 점입니다. LEFT JOIN을 통해 상품의 판매 유무와는 상관없이 모든 상품들이 조인한 테이블에 나타나게 됩니다. 그런 후, WHERE 절에서 product_id 가 존재하지 않는 경우를 찾음으로써 서브 쿼리의 아이디가 외부 쿼리 테이블에 존재하지 않는 경우를 재현하였습니다. 

 

4) EXISTS, NOT EXISTS 연사자 안의 상관관계 서브 쿼리

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

실제 예시를 살펴보도록 합시다. 아래 쿼리를 통해 2020년도에 팔리지 않은 상품들의 정보를 조회할 수 있습니다.

SELECT
 name,
 cost,
 city
FROM product
WHERE NOT EXISTS
 ( SELECT id 
   FROM sale 
   WHERE year = 2020 AND product_id = product.id );

결과는 다음과 같습니다.

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

위 쿼리는 외부 쿼리 테이블이 가지고 있는 상품들을 가지고 서브 쿼리의 조건과 일치하는지 확인합니다. 서브 쿼리가 정한 조건은 판매 연도가 2020년이면서 sale 테이블의 product_id와 외부 쿼리의 id 가 일치될 수 있도록 product 테이블의 id 가 sale 테이블에도 존재해야 한다는 것입니다. 하지만 사용된 연산자가 NOT EXISTS 이므로 서브 쿼리가 정한 조건과 정확히 일치하지 않으면 됩니다. 따라서 판매 연도가 2020년이 아님과 동시에 sale 테이블에 아무런 정보가 존재하지 않는 상품들이 해당 쿼리의 결과입니다.

이를 조인을 사용하여 다시 작성해보면 아래와 같습니다.

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;

여기서 LEFT JOIN을 통해 product 테이블과 sale 테이블을 연결하였습니다. LEFT JOIN을 사용했기 때문에 팔리지 않은 상품도 결과 테이블에서 찾아볼 수 있습니다. 그런 후 WHERE 절을 통해 1) sale 테이블에 아무런 정보가 없고(s.year IS NULL) 2) 판매 연도가 2020년이 아닌 값(s.year != 2000)들만 필터링해주었습니다.

 

다음 글에서는 서브 쿼리를 조인으로 대체할 수 없는 경우를 알아보도록 하겠습니다.

 

원문 : Subquery vs. JOIN