[SQL] SQL로 하는 데이터 분석 (3)_코드잇

[목차]

- 테이블 조인을 통한 깊이있는 데이터 분석

 

[조인(join)]

- 여러 테이블을 합쳐 하나의 테이블인 것처럼 보는 행위

 

ex) 

stock 테이블의 item_id 컬럼이 item 테이블의 id 컬럼을 참조하는 관계

- Foreign Key : 왜래키 → 다른 테이블의 특정 row를 식별할 수 있게 해주는 컬럼

- 참조를 하는 테이블인 stock 테이블을 ‘자식 테이블'

- 참조를 당하는 테이블인 item 테이블을 ‘부모 테이블'

- Foreign Key는 다른 테이블의 특정 row를 식별할 수 있어야 하기 때문에 주로 다른 테이블의 Primary Key를 참조할 때가 많음

 

[Foreign Key 설정하기]

- 두 테이블의 컬럼 간에 Foreign key 관계가 개념적으로 성립한다는 것과, Foreign key 관계가 실제로 설정되어있다는 것은 엄연히 다른 개념

- '개념적으로 성립' 한다는 것과 '실제로 그 관계를 DBMS로 설정'하는 것은 다름

- 설정 시 이점 : 에러로 예방 ex) 존재하지 않는 상품에 대한 재고 정보를 추가하려 하는 상황 방지

 

[JOIN]

- LEFT OUTER JOIN과 RIGHT OUTER JOIN을 묶어서 OUTER JOIN이라 부름 > OUTER JOIN은 어느 테이블이 기준점인지 주의

 

left outer join

 

 

right outer join

 

 

INNER JOIN : 두 테이블 다 조인 기준을 만족하는 컬럼 값이 있는 row

두 테이블 모두 공통이 되는 

NULL이 되는 경우 없음

 

 

[ALIAS]

- 칼럼 이름과 테이블 이름 모두에 alias를 붙일 수 있으나, 용도에서 차이 발생 

- 칼럼의 alias는 칼럼 이름이 실제로 alias로 변환되어 보여지게 하기 위한 용도

- 테이블의 alias는 SQL 문의 전체 길이를 줄여서 가독성을 높이기 위함 / 조인을 할 때 서로 다른 테이블의 같은 이름의 칼럼이 존재할 시 무슨 테이블의 칼럼인지를더 짧게 표현해주기 위해 사용

EX) member 테이블 / item 테이블 모두에 우연히 이름이 같은 gender 칼럼 존재

1) 전자 : 회원의 성별을 나타내기 위한 칼럼

2) 후자 : 상품이 여성용인지, 남성용인지를 나타내기 위한 칼럼

→ 만약 member 테이블에 m, item 테이블에 i라는 alias를 붙였다면

1)은 member.gender 대신 m.gender
2)는 item.gender 대신 i.gender

>> FROM 절에서 alias를 붙였으면, 다른 모든 절에서 그 테이블은 alias로만 나타내야 함 > 에러 발생

 

- Foreign Key를 기준으로 조인하면 하나의 OUTER JOIN(LEFT 또는 RIGHT)의 결과와 INNER JOIN의 결과가 같을 수밖에 없음

 

[실습] 

- 가격과 원가를 보여주는 pizza_price_cost 테이블

- 각 피자의 판매량 정보가 담긴 sales 테이블

> 두 테이블을 조인해서, 각 피자별 판매량을 한꺼번에 확인하고자 함

 

SELECT p.name, 
       COALESCE(s.sales_volume, '판매량 정보 없음') AS '판매량'
FROM pizza_price_cost AS p LEFT OUTER JOIN sales AS s ON p.id = s.menu_id;

 

[연산]

- 테이블을 합치는 연산은 크게 결합 연산과 집합 연산

- 결합 연산 : 테이블을 가로 방향으로 합치는 것에 관한 연산 → 조인

- 집합 연산 : 테이블을 세로 방향으로 합치는 것에 관한 연산 → 같은 종류의 테이블끼리만 가능

  ㄴ 테이블 하나를 집합 하나로 보고, 그 안의 각 row를 하나의 원소로 간주하고 진행되는 연산

 

(1) A ∩ B (INTERSECT 연산자 사용)

SELECT * FROM member_A
INTERSECT 
SELECT * FROM member_B

 

(2) A - B (MINUS 연산자 또는 EXCEPT 연산자 사용)

SELECT * FROM member_A 
MINUS
SELECT * FROM member_B

 

(3) B - A (MINUS 연산자 또는 EXCEPT 연산자 사용)

SELECT * FROM member_B
MINUS
SELECT * FROM member_A

 

(4) A U B (UNION 연산자 사용) > 두 집합이 공통으로 갖고 있는 원소는 중복 제거 후 하나만 표시
SELECT * FROM member_A
UNION
SELECT * FROM member_B

*MySQL에서는 버전 8.0 기준으로 UNION 연산자만 지원한다는 점

- INTERSECT, MINUS 연산은 조인을 사용해 간접적으로 원하는 결과 확보 가능

 

[JOIN - ON 대신 USING]

- 만약 조인 조건으로 쓰인 두 컬럼의 이름이 같으면 ON 대신 USING 사용 가능

- item 테이블의 id 컬럼과 item_new 테이블의 id 컬럼을 기준으로 조인 > 조인 조건으로 사용되는 칼럼의 이름이 같으면 USING으로 대체 가능 > ON old.id = new.id 와 USING(id)의 의미는 동일 

 

[UNION 더 알아보기]

1. 서로 다른 종류의 테이블도, 조회하는 컬럼을 일치시키면 집합 연산이 가능

- 두 테이블의 컬럼 수가 다를 경우, SELECT 절 뒤의 * 부분을 두 테이블이 공통적으로 갖고 있는 컬럼 이름들로 바꿔주고 실행

> 두 테이블의 원래 컬럼 구조가 달라도, 두 테이블이 공통적으로 갖고 있는 칼럼들만 조회한 경우 UNION 집합 연산 수행 가능

 

2. UNION 과 UNION ALL

- UNION은 두 테이블의 교집합에 해당하는 영역의 row들은 중복을 제거하고, 딱 하나의 row만 보여줌 > 정보 누락 가능성 

→ UNION ALL은 UNION처럼 두 테이블의 합집합을 보여준다는 점은 같으나, 겹치는 것을 중복 제거하지 않고, 겹치는 것들을 그대로 둘다 보여준다는 차이점

 

UNION 연산자 : 중복을 제거하고 깔끔하게 보는 것이 중요한 경우

UNION ALL 연산자 : 중복을 제거하게 되면 정보 누락이 발생할 수 있는 경우

 

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

 

1:1 관계 , 1:n 관계

- 하나의 상품에는, 여러 개의 리뷰 가능 > 1:n 관계

- '상품'과 '리뷰'처럼 1:n 관계인 경우에는 조인을 할 때 1:n 중 1에 해당하는 테이블의 row는 위 그림처럼 조인 결과에서 여러 번 중복 등장

 

[여러 테이블 조인하기 실습]

- 세 테이블을 조인하여 남녀 공용 상품의 등록 연도별 평균 별점 모니터링

 

SELECT YEAR(i.registration_date) AS '등록 연도', 
       COUNT(*) AS '리뷰 개수', 
       AVG(star) AS '별점 평균값'
FROM review AS r INNER JOIN item AS i ON r.item_id = i.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;

 

[다른 종류의 조인들]

두 테이블을 서로 합치는 연산에는 크게 두가지 종류 

1. 결합 연산 : 두 테이블을 가로 방향으로 합치는 것

→ LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN

2. 집합 연산 : 두 테이블을 세로 방향으로 합치는 것

→ INTERSECT, MINUS, UNION, UNION ALL 
* INTERSECT, MINUS 연산자는 MySQL에서 지원x 

 

※ 실무적 활용력은 떨어짐

- NATURAL JOIN(자연 조인) : 두 테이블에서 같은 이름의 컬럼을 찾아서 자동으로 그것들을 조인 조건을 설정하고, INNER JOIN을 해주는 조인 → 조인 조건을 자동으로 설정해주기 때문에 ON절 쓸 필요 없음

- CROSS JOIN : 한 테이블의 하나의 row에 다른 테이블의 모든 row들을 매칭하고, 그 다음 row에서도 또, 다른 테이블의 모든 row들을 매칭하는 것을 반복함으로써 두 테이블의 row들의 모든 조합을 보여주는 조인 → 카르테시안 곱(Cartesian Product)을 구하는 조인

- SELF JOIN : 셀프라는 단어의 뜻 그대로 테이블이 자기 자신과 조인을 하는 경우 →  조인 대상이 같은 테이블을 마치 별도의 테이블인 것처럼 간주하고 진행된다는 점에서 특색이 있는 조인

- FULL OUTER JOIN : 두 테이블의 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합치는 조인

- Non-Equi 조인 : 동등 조건이 아닌 다른 종류의 조건을 사

  ㄴ 조인 조건에 항상 등호(=)를 사용 > Equi 조인