📌 학습주제
1. JOIN - INNER JOIN/LEFT JOIN/RIGHT JOIN/FULL OUTER JOIN/CROSS JOIN
2. UNION - UNION/UNION ALL
3. WITH
4. Subquery
JOINS
: 특정 key(고유한 값으로 이루어진 컬럼)를 기준으로 2개 이상의 테이블을 결합하는 표현
Q. 테이블을 쪼개놓는 이유는?
A1. 테이블에 변경사항이 생겼을 경우 관리가 용이하다.
공유하고 있는 컬럼을 갖고 있는 A와 B테이블이 있다고 가정하자.
만약 A테이블의 해당 컬럼에 변경사항이 생겼을 경우, 매번 B테이블의 해당 컬럼도 같이 변경해주어야 하는 번거로움이 발생한다.
즉, 테이블을 쪼개두면, A테이블만 변경하고 JOIN을 활용해서 B테이블에도 최신화된 데이터를 불러올 수 있다.
A2. 같은 데이터를 중복으로 저장하는 것을 최소화할 수 있다.
JOIN의 유형
1. INNER JOIN
: 두 개의 테이블에서 일치하는 행만 가져와서 결합하는 조인 유형으로, 연결된 열에서 값이 일치하는 행만 결과에 포함된다.
= 두 테이블 간의 '교집합'을 반환한다.
- 교집합을 구하면서 대상이 되는 행 개수를 줄이기 때문에 일반적으로 다른 조인보다 연산 속도가 빠르다.
SELECT 테이블명.컬럼명
FROM 기준테이블명 INNER JOIN 조인대상이되는테이블명 ON 기준테이블명.기준이되는컬럼 = 조인대상이되는테이블명.조인대상이되는컬럼
** 테이블명.컬럼명 : 특정 테이블의 특정 컬럼을 지칭한다.
SELECT products.*, managers.name AS manager_name
FROM products INNER JOIN managers ON products.category = managers.managing→ 'products' 테이블의 'category'컬럼과 'managers' 테이블의 'managing' 컬럼을 INNER JOIN하여
'products' 테이블의 모든 컬럼과 'managers'컬럼의 'name'컬럼을 'manager_name'이라는 새로운 컬럼명으로 결합하여 반환한다.
2. LEFT JOIN
: 왼쪽 테이블의 모든 행을 가져와서 오른쪽 테이블의 일치하는 행과 결합하는 조인 유형으로, 오른쪽 테이블에서 왼쪽 테이블과 일치하지 않는 행은 (null) 값으로 표시된다.
- 왼쪽 테이블의 모든 행을 포함하므로 조인으로 인하여 (왼쪽 테이블을 기준으로) 누락되는 행이 없다.
- 두 테이블의 key 값이 일치하지 않는 경우 (null)값으로 표시되기 때문에 행의 일치 여부를 알 수 있다.
- 좌측 테이블의 1개 행과 일치하는 행이 우측 테이블에 여러개 행과 일치할 경우 테이블이 커질 수 있다.
SELECT 테이블명.컬럼명
FROM 기준테이블명 LEFT JOIN 조인대상이되는테이블명 ON 기준테이블명.기준이되는컬럼 = 조인대상이되는테이블명.조인대상이되는컬럼
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→ 'clicks' 테이블의 'user_name' 컬럼에 'orders' 테이블의 'user_name' 컬럼을 LEFT JOIN하고,
'clicks' 테이블의 'product_id' 컬럼에 'orders' 테이블의 'product_id' 컬럼을 LEFT JOIN하고,
'clicks' 테이블의 'date' 컬럼에 'orders' 테이블의 'date' 컬럼을 LEFT JOIN하고,
'clicks' 테이블의 모든 컬럼과 'odr_index' 컬럼을 결합하여 반환한다.
3. RIGHT JOIN
: 오른쪽 테이블의 모든 행을 가져와서 왼쪽 테이블의 일치하는 행과 결합하는 조인 유형으로, 왼쪽 테이블에서 오른쪽 테이블과 일치하지 않는 행은 (null) 값으로 표시된다.
SELECT 테이블명.컬럼명
FROM 기준테이블명 RIGHT JOIN 조인대상이되는테이블명 ON 기준테이블명.기준이되는컬럼 = 조인대상이되는테이블명.조인대상이되는컬럼
4. FULL OUTER JOIN
: 두 테이블 간의 모든 행을 가져와서 일치하는 행과 일치하지 않는 행을 모두 포함하도록 결과를 반환하는 조인 유형으로, 두 테이블 간에 일치하지 않는 컬럼 값에는 (null) 값으로 표시된다.
- 두 테이블 간의 모든 행을 포함하기 때문에 필터링 되는 행이 없다.
따라서 결과 집합이 크거나 복잡한 경우, FULL OUTER JOIN을 사용하게 되면 성능 문제가 발생할 수 있으니 유의해야 한다!
- DB 종류마다 FULL OUTER JOIN의 지원 여부가 다르다.
(※ MySQL은 FULL OUTER JOIN을 지원하지 않음 → 따라서 각 테이블에 동일한 조건으로 LEFT JOIN, RIGHT JOIN을 적용한 다음 UNION으로 결합해야 한다.)
- FULL OUTER JOIN = INNER JOIN + LEFT JOIN + RIGHT JOIN (※ 단, 중복되는 행은 제외된다.)
5. CROSS JOIN (=Cartesian product)
: 두 테이블 간의 가능한 모든 조합을 생성한 후 이에 대한 집계를 수행할 때 사용하는 조인 유형.
- 상품 간 유사도를 구하여 연관 상품을 추천할 때 사용된다.
- FULL OUTER JOIN 보다도 연산의 양이 많기 때문에 무분별하게 사용할 경우 DB에 과부하가 생기고 비용과 시간이 많이 청구되어 업무 비효율화를 초래할 수 있다.
- CROSS JOIN 결과 테이블 행 개수 = (왼쪽 테이블 행 개수) x (오른쪽 테이블 행 개수)
- 유사도 구하는 방법에는 여러 방법이 있지만, 가장 단순한 것은 각 테이블의 공통된 데이터를 갖는 행에 대하여 점수를 부여할 수 있다.
SELECT 테이블명.컬럼명
FROM 기준테이블명 CROSS JOIN 조인대상이되는테이블명
** Alias (별칭)
: 테이블명이 길어질 경우 별칭을 활용해서 편리하고 구분이 쉬워진다.
SELECT 별칭.컬럼명
FROM 테이블1 별칭1 JOIN 테이블2 별칭2 ON 별칭1.컬럼명 = 별칭2.컬럼명
6. SELF JOIN
: 하나의 테이블을 자기 자신과 결합시키는 조인 유형
- SELF JOIN 이라는 구문이 따로 있는 것이 아니라, 기존의 JOIN 유형을 활용하되 자기 자신과 결합하는 경우를 SELF JOIN이라 통칭한다.
- 동일한 테이블 내에 있는 데이터를 비교 및 연결할 때 사용된다.
ex. 직원 정보가 들어있는 테이블에서 각 직원별로 직속 상사를 연결하고 싶을 때 사용한다.
- SELF JOIN을 사용할 때는 테이블명과 컬럼명이 모두 겹치기 때문에 ALIAS(별칭) 사용이 필수이다.
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→ 기준 테이블이자 대상이 되는 테이블인 'mangers_v2' 테이블을 'm1'과 'm2'라는 별칭으로 설정하고
'm1' 의 'substitute' 컬럼과 'm2'의 'id' 컬럼을 INNER JOIN하여
'm1' 테이블의 모든 컬럼과
'm2' 테이블의 'id'컬럼을 'sub_id'라는 새로운 컬럼명으로 설정한 컬럼과
'm2' 테이블의 'name'컬럼을 'sub_name'이라는 새로운 컬럼명으로 설정한 컬럼을 결합하여 반환한다.
✓ JOIN은 '필터링'이 가능하다.
조인의 결과물로 나오는 것은 테이블이다.
쿼리 결과물이 임시테이블로 저장되었다가 세션이 끝나면 삭제되는 방식으로 동작한다.
따라서, 기존 테이블에 사용할 수 있었던 구문들을(WHERE) 그대로 적용할 수 있다.
UNION
: 두 개 이상의 SELECT문의 결과를 결합하여 하나의 결과 집합으로 만드는데 사용된다.
- UNION 사용을 통해 하나의 쿼리 내에서 SELECT를 여러번 사용할 수 있다.
- SELECT에 포함된 전체 컬럼을 대상으로 중복되는 행을 제거해준다. (즉, 컬럼 전체가 같아야 중복 제거가 가능하다.)
- UNION을 사용하기 위해서는 각 SELECT문의 결과 집합에 포함되는 열 개수와 데이터 타입이 일치해야 한다.
- 테이블명은 일치하지 않아도 된다.
- UNION의 결과도 테이블이기 때문에 WHERE문을 사용하여 '필터링' 가능하다.
(단, UNION을 사용하는 테이블은 컬럼명이 같기 때문에 WHERE문의 조건을 걸 때 테이블명.컬럼명을 통해서 테이블명을 명시해주어야 한다.)
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n
FROM 테이블1
WHERE 테이블명.컬럼명 ~ (조건)
UNION
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n -- 테이블1과의 '컬럼(열) 개수'와 '데이터 타입'이 일치해야 함
FROM 테이블2
WHERE 테이블명.컬럼명 ~ (조건)
UNION ALL
- 중복 제거가 불가능하다.
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n
FROM 테이블1
UNION ALL
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n -- 테이블1과의 '컬럼(열) 개수'와 '데이터 타입'이 일치해야 함
FROM 테이블2
MySQL에서의 FULL OUTER JOIN
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n
FROM 테이블1 LEFT JOIN 테이블2 ON 테이블1.컬럼명 = 테이블2.컬럼명
UNION
SELECT 컬럼명1, 컬럼명2, ... 컬럼명n -- 테이블1과의 '컬럼(열) 개수'와 '데이터 타입'이 일치해야 함
FROM 테이블1 RIGHT JOIN 테이블2 ON 테이블1.컬럼명 = 테이블2.컬럼명
WITH
: 임시 결과 집합을 생성하여 복잡한 쿼리 작성을 쉽고 편리하게 돕는다.
- WITH 구문을 사용하므로써 복잡한 쿼리도 블록 단위로 쪼개서 작성할 수 있기 때문에 코드 이해가 훨씬 쉬워진다.
- 각 블록은 다른 블록에서도 테이블처럼 지칭해서 사용할 수 있기 때문에, 같은 부분을 불러오는 쿼리를 여러번 실행하지 않아도 된다. 따라서 현업에서 쿼리를 작성할 때는 WITH 구문을 적극적으로 활용해보자!
WITH 블록명 AS(
SELECT 컬럼명
FROM 테이블1 별칭1 INNER JOIN 테이블2 별칭2 ON 별칭1.컬럼명 = 별칭2.컬럼명
GROUP BY
ORDER BY
)
SELECT 컬럼명
FROM 블록명 별칭3 INNER JOIN 테이블4 별칭4 ON 별칭3.컬럼명 = 별칭4.컬럼명
예제로 좀 더 살펴보자.
아래의 SQL 실습 페이지를 활용했다. 예제로 적용할 수 있는 테이블을 기본적으로 제공해주는 것 같다.
https://www.programiz.com/sql/online-compiler/
우리가 원하는 결과물은 고객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 shippipng_cnt
FROM odr_cnt oc LEFT JOIN ship_cnt sc ON oc.customer_id = sc.customer_id
실행 결과
Subquery
: 다른 쿼리 내부에 포함된 쿼리로, 주로 더 큰 쿼리의 일부로 사용된다.
- 복잡한 조건을 사용하여 데이터를 추출하거나 다양한 테이블간의 관계를 분석하는 등 다양한 쿼리 작업에 사용할 수 있다.
- 잘못 사용할 경우 리소스를 지나치게 많이 사용할 수 있고 DB성능에 악영향을 미칠 수 있으므로 쿼리 최적화에 신경써서 작업해야 한다.
예제 1. SELECT 구문에서 Subquery를 사용하는 케이스
유저별로 평균 구매가격을 구한 후, 그것을 전체 평균 구매 가격과 비교하고 싶을 때 사용한다.
Subquery의 결과물을 하나의 컬럼으로 반환한 것
예제를 통해 살펴보자.
대상이 되는 테이블은 아래 두 가지 테이블이다.
SQL 쿼리를 작성하면 다음과 같다.
이 때, 팁은 바깥쪽 쿼리부터 먼저 작성하는 것이다!
큰 틀을 먼저 잡고 그 안에 서브쿼리를 작성하는 식으로 구조를 세우면 훨씬 파악하기 쉽다.
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
실행 결과
예제 2. FROM 구문에서 Subquery를 사용하는 케이스
'스포츠', '주방용품'을 담당하는 매니저들이 어떤 상품을 클릭했는지 파악하고 싶을 때
조인하기 전에 필터링을 먼저 하기 때문에 테이블의 크기를 줄여놓고 조인을 수행할 수 있다.
즉, 조인해야하는 테이블의 크기가 클수록 미리 필터링을 하면 DB의 성능을 최적화시킬 수 잇다.
SELECT c.*
FROM (SELECT name FROM managers WHERE managing in ('스포츠', '주방용품')) a -- 매니저 이름만 있는 임시 테이블을 a라고 지칭
INNER JOIN clicks c ON a.name = c.user_name
실행 결과
필터링을 먼저 한 후에 조인을 했기 때문에 예제 1보다 2가 더 효율적이다.
예제 3. WHERE 구문에서 Subquery를 사용하는 케이스
가상 비싼 상품에 대한 클릭 이력 가져오기
clicks 테이블에서 모든 컬럼을 가져오되, product_id가 서브쿼리와 일치하는 경우만 가져오는 것
** ALL()
: 주로 Subquery와 함께 사용하는 함수로, 전체 값을 비교해서 모두 만족하면 True를 반환한다.
SELECT c.*
FROM clicks c INNER JOIN products p ON c.product_id = p.product_id
WHERE price >= ALL(SELECT price FROM products) -- price값이 최대일때만 True를 반환
** EXIST()
: 주로 Subquery와 함께 사용하는 함수로, 서브쿼리 실행 결과가 있으면 True, 없으면 False를 반환한다.
SELECT o.*
FROM orders o
WHERE EXISTS(SELECT user_name
FROM orders o
INNER JOIN managers m ON o.user_name = m.name)
