[데브코스][데이터 분석] SQL 여러 테이블 결합하여 사용하기

< 3주차 SQL 쿼리 문법을 활용하여 데이터 분석을 진행하기 (2) >

 

3. 여러 테이블 결합하여 사용하기

3-1. 다양한 JOINS

 

JOIN: 두 개 이상의 테이블을 특정 key를 기준으로 결합하는 것

출처: 프로그래머스 데브코스 데이터분석 2기 수업자료 중 일부

 

테이블을 쪼개놓는 이유

: 각 테이블을 효율적으로 관리, 저장하기 위해

-> 테이블에 저장한 데이터에 변경사항이 있을 수 있기 때문

+ 같은 데이터를 중복 저장하는 것 방지. 

 

INNER JOIN

: 두 개의 테이블에서 일치하는 행만 가져와서 JOIN

즉, 연결된 열에서 값이 일치하는 경우만 반환(교집합)

컬럼값이 비어있거나 한 쪽에만 있는 행은 결과에서 제외됨

-> 특정행이 결과에 포함되어 있지 않으면 JOIN 조건 확인 필요

==> 교집합을 구하면서 대상 행 수를 줄이기 때문에 다른 JOIN보다 속도가 빠름

-- products 테이블을 기준으로 managers 테이블을 결합
-- products.category = managers.managing : 기준이 되는 열
SELECT products.*, managers.name as manager_name
FROM products INNER JOIN managers 
 ON products.category = managers.managing

-- AND를 통해 다중 조건 부여 가능
SELECT clicks.*, 1 as orderd
FROM clicks INNER JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.products_id
 AND clicks.date = orders.date

-- AND를 통해 다중 조건 부여 가능
SELECT clicks.*, 1 as orderd, name
FROM clicks INNER JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.products_id
 AND clicks.date = orders.date
-- 상품이름 추가 결합
INNER JOIN products on clicks.product_id = products_id

 

LEFT JOIN

: 왼쪽 테이블의 모든 행을 가져오고, 오른쪽 테이블에서 일치하는 행을 가져옴

오른쪽 테이블의 일치하지 않는 값은 NULL으로 표시

왼쪽 테이블의 행을 모두 포함하므로, JOIN으로 인해 누락되는 데이터가 없음

- 어떤 행이 일치하는지, 안 일치하는데 알 수 있음

-- clicks 테이블을 기준으로 결합하되, orders 테이블의 user_name, products_id, date가 모두 같아야 결합함
-- clicks 테이블 중 odr_index가 있는 사람은 odr_index를 뽑아오고, 없는 사람은 null로 표시
SELECT clicks.*, odr_index
FROM clicks LEFT JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.product_id
 AND clicks.date = orders.date

: odr_index가 있는 행은 클릭 + 구매까지 이어진 사람이고,

odr_index가 null인 행은 상품을 클릭했지만, 구매는 하지 않은 사람들임

 

-- clicks 테이블을 기준으로 결합하되, orders 테이블의 user_name, products_id, date가 모두 같아야 결합함
-- clicks 테이블 중 odr_index가 있는 사람은 odr_index를 뽑아오고, 없는 사람은 null로 표시
-- + 상품 이름을 추가해보자
SELECT clicks.*, odr_index, name
FROM clicks LEFT JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.product_id
 AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id

 

JOIN의 결과를 바탕으로 나온 테이블을 필터링해보자. 

-- clicks 테이블을 기준으로 결합하되, orders 테이블의 user_name, products_id, date가 모두 같아야 결합함
-- clicks 테이블 중 odr_index가 있는 사람은 odr_index를 뽑아오고, 없는 사람은 null로 표시
-- + 상품 이름을 추가해보자
-- + JOIN의 결과물을 필터링해보자
SELECT clicks.*, odr_index, name, category
FROM clicks LEFT JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.product_id
 AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id
WHERE products.category = '디지털'

 

: 디지털 카테고리에 포함되는 상품을 구매/판매 여부를 확인할 수 있음

 

!! 클릭만 하고, 당일에 구매하지 않은 상품들을 찾아보자. 

-> AND 조건 추가! 

-- clicks 테이블을 기준으로 결합하되, orders 테이블의 user_name, products_id, date가 모두 같아야 결합함
-- clicks 테이블 중 odr_index가 있는 사람은 odr_index를 뽑아오고, 없는 사람은 null로 표시
-- + 상품 이름을 추가해보자
-- + JOIN의 결과물을 필터링해보자
-- 클릭만 하고, 당일에 구매하지 않은 상품들을 찾아보자. 

SELECT clicks.*, odr_index, name, category
FROM clicks LEFT JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.product_id
 AND clicks.date = orders.date
INNER JOIN products on clicks.product_id = products.product_id
WHERE products.category = '디지털'
AND odr_index is NULL

 

RIGHT JOIN

: LEFT JOIN의 반대. 

오른쪽 테이블의 모든 행을 가져오고, 왼쪽 테이블에서 일치하는 행을 가져와 결합함. 

오른쪽 테이블의 모든 데이터를 포함하게 됨. 

-- orders 테이블을 기준으로 결합하되, 
-- orders 테이블의 모든 값을 가져오고, clicks 테이블에서 user_name, products_id, date가 모두 같아야 결합함
-- clicks 테이블 중 odr_index가 있는 사람은 odr_index를 뽑아오고, 없는 사람은 null로 표시
SELECT clicks.*, clk_index
FROM clicks RIGHT JOIN orders
 ON clicks.user_name = orders.user_name
 AND clicks.product_id = orders.product_id
 AND clicks.date = orders.date

영희가 20231014에 click을 2번 했기 때문에, orders 테이블에는 한 행만 있던 영희의 데이터가 2개의 행으로 늘어남. 

 

FULL OUTER JOIN

: 두 테이블간의 모든 행을 가져오며, 일치하는 행과 일치하지 않는 행 모두를 포함하는 결과를 반환

결과집합이 크거나 복잡한 경우 성능 저하를 가져올 수 있음. 꼭 필요한 경우만 제한적으로 사용

-> DB마다 지원유무가 다름

(MySQL은 지원X ==> LEFT JOIN, RIGHT JOIN을 한 후, UNION으로 결합하면 사용 가능)

SELECT orders.*, clk_index
FROM clicks LEFT JOIN orders
	on clicks.user_name = orders.user_name
    and clicks.product_id = orders.product_id
    and clicks.date = orders.date
UNION
SELECT orders.*, clk_index
FROM clicks RIGHT JOIN orders
	on clicks.user_name = orders.user_name
    and clicks.product_id = orders.product_id
    and clicks.date = orders.date

 

==> 결론적으로 FULL OUTER JOIN의 결과는

INNER JOIN, LEFT JOIN, RIGHT JOIN의 결과를 모두 위아래로 붙인 것과 같음

 

 

CROSS JOIN (=Cartesian product)

: 두 테이블 간의 가능한 모든 조합을 생성할 때 사용

-> 두 테이블 간의 모든 조합을 생성한 뒤 집계를 해야할 때 주로 사용

ex. 상품을 클릭한 시간과 상품을 구매한 시간 차이의 평균을 구하고 싶을 때, 상품간 유사도를 구할 때 등 사용! 

-- clicks 테이블과 orders 테이블에서 컬럼이름이 겹치는 경우를 해결함
-- ON 조건 필요 X
SELECT clicks.*, 
		odr_index, 
        orders.product_id as odr_product_id
        orders.user_name as odr_user_name, 
        orders.date as odr_date
FROM clicks CROSS JOIN orders

-> cross join 결과는 연산량이 매우 많아지기에 (모든 조합 생성) 과부하가 올 수 있음

 

Alias (=별칭)

: 테이블에 별칭을 붙여 가독성있게 만들 수 있음

-> 테이블을 사용하기 쉽고 같은 테이블끼리 결합시 편리함

-- clicks 테이블은 c, orders 테이블은 o로 별칭
SELECT c.*, 
		o.odr_index, 
        o.product_id as odr_product_id
        o.user_name as odr_user_name, 
        o.date as odr_date
FROM clicks c CROSS JOIN orders o

 

SELF JOIN

: 하나의 테이블을 자기 자신과 결합하는 방법

(구문이 따로 있지는 않음) (Alias를 필수적으로 사용하여 테이블에 별칭을 줘야함!!)

-> 동일한 테이블 내의 데이터를 비교하거나 연결할 때 사용

ex. 직원 정보가 들어있는 테이블에서 각 직원별 직속상사를 연결하고 싶을 때 사용

SELECT m1.*, m2.id as sub_id, m2.name as sub_name
FROM managers_v2 m1 INNER JOIN managers_v2 m2
ON m1.substitute = m2.id

 

필터링

: join의 결과물로 나온 테이블을 where문을 통해 필터링함

(JOIN을 필터링하는 것보다 필터를 먼저 걸고, JOIN하는 것이 더 좋음)

 

 

다양한 JOIN 총 정리

3-2. UNION

두 개 이상의 select문의 결과를 결합하여 하나의 결과 집합으로 만드는데 사용됨. 

간단하게, select한 결과문을 위아래로 붙이는 것을 의미함. 

=> 두 개 이상의 select 결합 가능

MySQL에서 FULL OUTER JOIN을 한 것과 같음! 

<특징>

1. 중복되는 행을 제거해줌

2. 각 select 문의 결과 집합에 포함되는 열의 수와 데이터 타입이 일치해야함. 

-- products 테이블에서 뽑는 컬럼 수(*)와 product_B 테이블에서 뽑는 컬럼 수(*)가 같음
SELECT *
FROM products
UNION
SELECT *
FROM product_B

 

UNION ALL

: UNION과 달리 중복제거를 하지 않음

즉, 중복제거 없이 단순히 위아래로 붙이기만 함 

 

3-3. WITH

1. CTE (Common Table Expression) 라고도 부르며, MySQL 8.0 버전 이상에서 지원한다.

2. 임시 결과 집합을 생성하여 복잡한 쿼리를 쉽게 작성할 수 있도록 돕는 기능을 한다.

3. 복잡한 쿼리에서 하위 쿼리를 사용해 같은 결과를 여러 번 계산해야 하는 경우를 줄여 준다.

- CTE는 같은 쿼리 블록을 여러 번 사용할 수 있도록 함

4. 쿼리 가독성을 높여 유지보수를 용이하게 한다.

5. DB Optimizer는 CTE를 단순한 뷰나 서브쿼리보다 더 효율적으로 처리한다.

=> WITH 구문은 복잡한 쿼리도 쪼개서 작성할 수 있기 때문에 코드를 이해하기 훨씬 쉬움

 

-- with 쿼리 예제 코드
-- 만든 CTE user_orders을 마치 테이블처럼 사용함
WITH user_orders as (
	SELECT user_name, SUM(price) as total_purchase
    FROM orders o INNER JOIN products p 
    	ON o.product_id = p.product_id
        GROUP BY 1
        ORDER BY 2 DESC
)
SELECT user_orders.*
FROM user_orders uo INNER JOIN managers m 
	ON uo.user_name = m.user_name

 

기존에 실습하던 웹사이트는 MySQL 5.x 버전이므로, 아래의 사이트에서 실습 진행! 

https://www.programiz.com/sql/online-compiler/

Online SQL Editor

+ 사이트내에서 기본으로 제공되는 DDL을 이용해보자. 

-- 고객별 구매 상품수, 총 구매금액, 배송상태가 pending인 shipping_id 조회
WITH odr_cnt as (
	SELECT c.customer_id, count(distinct order_id) as odr_cnt, sum(amount) as total_purchase
    FROM Customers c INNER JOIN Orders o on c.customer_id = o.customer_id
    GROUP BY 1
    ORDER BY 2 DESC
), 
ship_cnt as (
	SELECT c.customer_id, count(distinct shipping_id) as ship_cnt
    FROM Customers c INNER JOIN Shippings s on c.customer_id = s.customer
    WHERE status = 'Pending'
    GROUP BY 1
    ORDER BY 2 DESC
)
SELECT oc.customer_id, odr_cnt, total_purchase, COALESCE(ship_cnt, 0) as shipping_cnt
FROM odr_cnt oc LEFT JOIN ship_cnt sc on oc.customer_id = sc.customer_id

: 4번 user는 주문 번호 기준으로 2번 구매, 총 구매 금액은 700, 배송이 pending된 수는 1개임

 

3-4. Subquery

: 다른 쿼리 내부에 포함된 쿼리로, 주로 더 큰 쿼리의 일부로 사용됨

복잡한 조건을 사용하여 데이터를 추출하거나, 다양한 테이블간의 관계를 분석하는 등에 사용. 

그러나, 잘못 사용할 경우 리소스를 지나치게 많이 사용할 수 있고, 성능저하가 따라올 수 있음

 

ex 1) 유저별 평균 구매 가격과 전체 평균 구매 가격 비교하기

SELECT user_name, 
		AVG(price) as avg_price, 
		(SELECT AVG(price)
     		FROM orders o INNER JOIN products p
     		ON o.product_id = p.product_id) as total_avg_price
FROM orders o INNER JOIN products p
ON o.product_id = p.product_id
GROUP BY 1
ORDER BY 2 DESC

 

ex 2) 스포츠/주방용품 매니저들의 클릭 이력을 가져오기

SELECT c.*
FROM (SELECT name
	  FROM managers
      WHERE managing in ('스포츠', '주방용품')) a
INNER JOIN clicks c on a.name = c.user_name

 

ex 3) 가장 비싼 상품에 대한 클릭 이력 가져오기

-> 아래 두 개의 쿼리문이 같은 결과를 보임

SELECT c.*
FROM clicks c
WHERE product_id = (SELECT product_id FROM products
                    ORDER BY price DESC 
                    LIMIT 1)

SELECT c.*
FROM clicks c INNER JOIN products p
ON c.product_id = p.product_id
WHERE price >= ALL(SELECT price FROM products)

 

ex 4) 매니저들 중에 상품을 구매한 사람이 있는 경우, 구매 테이블 전체를 출력하기

: 서브쿼리와 exists를 함께 사용함

SELECT o.*
FROM orders o
WHERE EXISTS (SELECT user_name 
				FROM orders o INNER JOIN managers m
                ON o.user_name = m.name)