[Codeit/MySQL] #5 테이블 조인을 통한 데이터 분석

테이블 간의 연결고리

우선 테이블 column간의 연관성을 파악하는 것이 중요하다

이런 식으로 연결고리를 찾자

~stock (자식)테이블의 item_id가 item (부모)테이블의 id 컬럼을 참조하는 경우

이렇게 다른 테이블의 row를 식별해주는 컬럼을 Foreign Key(외래키)라고 한다

 

파악이 되었다면 Foreign Key를 설정해보자

table의 Setting에서 Foreign Keys를 선택하고 Foreign Key Name을 설정한다

item을 참조하는 stock의 fk이므로 ‘fk_stock_item’으로 지어준다

다음으로 Referenced Table을 클릭해 참조되는 Table을 선택하고 우측에서 컬럼들을 연결 짓는다

Apply!

 

이런 식으로 외래키 지정을 해 두면 이상한 row가 추가되는 것을 방지할 수 있다!

외래키 지정은 부모테이블 ~ 자식테이블 관계에서만 가능!

 

다른 종류의 테이블 조인하기

테이블 조인(join)이란 여러 테이블을 합쳐서 하나의 테이블처럼 보는 것이다

SELECT
	item.id,
    item.name,
    stock.item_id,
    stock.inventory_count
FROM item LEFT OUTER JOIN stock
ON item.id = stock.item_id

FROM A LEFT OUTER JOIN B

ON a = b          -> A테이블을 기준으로 B테이블이 동일한 a, b 컬럼 값으로 대응되며 합쳐짐

 

FROM A RIGHT OUTER JOIN B

ON a = b          -> B테이블을 기준으로 A테이블이 동일한 a, b 컬럼 값으로 대응되며 합쳐짐

*컬럼 이름이 동일하다면 USING(id)라고 써도 된다

 

테이블을 합친 후 알아보기 쉽게 테이블에 alias를 붙어보자

SELECT
	i.id,
    i.name,
    s.item_id,
    s.inventory_count
FROM item AS i LEFT OUTER JOIN stock AS s
ON item.id = stock.item_id

이렇게 AS로 alias를 지정하고 코드의 모든 절에 동일하게 수정해줘야 한다

*컬럼에 붙이는 alias와 달리 표에 나타나지는 않는다.

단순히 코드 가독성, 서로 다른 테이블에 같은 이름의 컬럼을 구별하려는 목적이다

 

SELECT
	item.id,
    item.name,
    stock.item_id,
    stock.inventory_count
FROM item INNER JOIN stock
ON item.id = stock.item_id

FROM A INNER JOIN B

ON a = b                         -> 기준 없이 합치고, 값이 겹쳐지는 컬럼만 보여준다(Null X)

*Foreign Key를 기준으로 합쳤다면 RIGHT OUTER JOIN과 INNER JOIN결과가 같을 수 밖에 없다!

 

물론 반드시 부모 자식 관계가 아닌 table이라도 공통요소가 보인다면 합칠 수 있다!

그런 경우 RIGHT OUTER JOIN/LEFT OUTER JOIN/INNER JOIN의 결과가 각각 다를 것

 

이렇게 다른 종류의 테이블을 가로방향으로 합치는 것을 ‘결합 연산’이라고 한다

 

같은 종류의 테이블 조인하기

같은 종류의 테이블을 세로방향으로 합치는 ‘집합 연산’에 대해서 알아보자

말 그대로 row들을 원소로 가지는 두 집합의 합집합, 차집합, 교집합을 구한다고 생각하면 간단!

*INTERSECT와 MINUS는 MySQL에서 지원하지 않는다!

JOIN을 통해서 간접적인 결과를 얻자~

 

UNION -> 교집합이 존재하는 같은 종류의 두 테이블을 합칠 때 사용!

SELECT * FROM item
UNION
SELECT * FROM item_new

UNION ALL -> 교집합을 고려하지 않고 그냥 두 테이블을 합칠 때 사용!

 

사실 다른 종류의 테이블도 조회하는 컬럼만 일치시키면 집합 연산이 가능하다

이런 식으로 두 테이블의 공통 컬럼만 뽑는 것이다

서로 다른 3개의 테이블 조인하기

이런 식으로 3개 이상의 테이블이 연결고리를 가지는 경우가 있다

이 경우 review 테이블에 2개의 Foreign Key 설정이 가능

SELECT
	i.name, i.id,
    r.item_id, r.star, r.comment, r.mem_id,
    m.id, m.email
FROM
	item As i LEFT OUTER JOIN review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN member AS m
		ON r.mem_id = m.id;

LEFT OUTER JOIN 을 활용해 2번 붙이는 구조

이제, 여기서 의미 있는 데이터를 추출해보자

 

ex) 각 상품별로 여성회원들이 남긴 별점의 평균값, 리뷰 수 구하기 (리뷰가 하나인 경우 제외)

SELECT i.id, i.name, AVG(star), COUNT(*)
FROM
	item AS i LEFT OUTER JOIN review AS r
		ON r.item_id = i.id
	LEFT OUTER JOIN member AS m
		ON r.mem_id = m.id
WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING COUNT(*) > 1
ORDER BY 
	AVG(star) DESC,
    	COUNT(*) DESC;

ex) 이번에는 남녀 공용 상품의 등록 연도별 평균 별점을 구해보자

SELECT 
    YEAR(i.registration_date) AS '등록 연도',
    COUNT(*) AS '리뷰 개수',
    AVG(star) AS '별점 평균값'
FROM 
    item AS i INNER JOIN review AS r
    ON i.id = r.item_id
    INNER JOIN member AS m
    ON r.mem_id = m.id
WHERE i.gender = 'u'
GROUP BY YEAR(i.registration_date)
HAVING COUNT(*) > 10
ORDER BY AVG(star) DESC;

이 외에도 NATURAL JOIN, CROSS JOIN, SELF JOIN, FULL OUTER JOIN, NON-Equi JOIN 등이 있다..

 

본 내용은 Codeit의 '개발자를 위한 SQL 데이터베이스'를 바탕으로 작성되었습니다.