[이것이 MYSQL이다] 7장 중후반 정리 (pivotTable, Join)

7장 주요내용

mysql 7장에서는 대표적으로 sql프로그래밍, join, pivot에 대하여 배우게 되었다.

 

sql 고급문을 다룬 내용이므로 이해하기 어려운 부분이 있었지만 잘 소화하였던것 같다.

 

특히 join이라는 개념은 mysql과 다른 관계형 데이터베이스에서 무지막지 하게 많이 쓰이는 개념이므로 정말 중요하다.

Pivot Table (피벗 테이블)

피벗테이블은 데이터의 통계나 집계를 하기 위하여 많이 쓰이는 형식이다.

 

간단하게 쇼핑몰의 구매 테이블을 생각하게 된다면 한 유저는 여러 물건을 구입할수 있게 된다.

 

아래와 같은 테이블이 될것이다.

 

위와 같이 같은 유저 아이디로 여러개의 물건을 구입할 수 있다.

 

하지만 이러한 통계는 눈에 잘 보이지 않는다.

 

이제 이걸 pivot table로 나타내보자.

SELECT id,
	SUM(IF(prod="냉장고", cnt, 0)) AS '냉장고', -- mysql에서 if문 처리는 삼항연산자와 비슷하다.
	SUM(IF(prod="에어컨", cnt, 0)) AS '에어컨',
	SUM(IF(prod="선풍기", cnt, 0)) AS '선풍기',
	SUM(prod) AS '합계'
		FROM prodTbl 
		GROUP BY id;

 

집계함수와 조건문을 이용해서 pivot table을 만들 수 있다.

 

이 명령어를 실행하면 아래와 같은 데이터가 나오게 된다.

 

집계함수와 조건문을 이용하면 중복되는 열의 값들을 순환해서 해당 열들을 여러 열로 반환하여 집계하는 테이블을 피벗 테이블이라고 할 수 있다.

Inner Join

정말 많이 쓰이는 조인방식이다 mysql에서는 일반적으로 join이라고만 해도 inner join이 된다.

 

예를 들어서 A테이블과 B테이블이 있으면 두 테이블 사이의 같은 값을 묶어서 조인하는 방식이다.

 

유저 테이블과 구매 테이블은 서로 유저 아이디로 관계가 있다.

 

조인에서는 ON이라는 조인 조건문을 이용하여 서로의 테이블과 연관되는 ROWS를 비교한다.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userId = B.userId
	ORDER BY U.userId;

 

일반적인 Inner join 방식이다. U와 B는 각각 테이블의 별칭이다.

 

별칭이란 각각의 테이블 이름을 붙여서 지정하면 코드의 길이도 늘어나고 이해하기가 힘들것이다.

 

따라서 각각의 테이블에 특별한 별칭을 붙이고 그 별칭으로 열을 가져온다.

 

코드도 엄청나게 간결해지고 이해하기도 쉽다. 정말 많이 쓰이므로 별칭은 잘 기억하자.

 

ON을 이용하여서 buytbl, usertbl 모두 서로의 아이디가 일치한 rows가 존재하는 데이터만 가져온다.

 

만약 유저 테이블에는 데이터가 있는데 구매 테이블에는 해당 유저 아이디와 일치한 row가 없으면 조인되지 않는 방식이 Inner Join이다.

 

만약 3개의 테이블로 N:M관계 조인을 하고 싶은 경우에는 아래와 같이 하면된다.

SELECT U.userId, B.prod, A.adminName, U.addr
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userId = B.userId
		Inner JOIN admintbl A
			ON B.number = A.number
	ORDER BY U.userId;

 

N:M 조인을 할 겅우에는 2개의 테이블을 엮기 위해서 첫번째로 유저 테이블과 구매 테이블로 조인하고 구메 테이블과 관리 테이블을 조인하여 관계를 설정해주어야 한다.

Outer Join

outer join은 평균적으로 많이 쓰이지는 읺는다. 하지만 알아두면 좋으므로 알아두자.

 

outer join은 크게 3가지로 분류할 수 있다. left join, right join, full join이 있다.

left join

왼쪽 테이블을 기준으로 **"왼쪽 테이블에 있는 데이터는 모두 가져와라."**라는 말과 같다.

 

Inner join에서는 두 테이블과 조건이 일치한 행만 조인됬다면 이번에는 왼쪽에 있는 테이블의 값은 모두 가져온다. 코드 예시는 아래와 같다.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		LEFT JOIN buytbl B
			ON U.userId = B.userId -- 이 조건이 걸리지 않으면 NULL로 처리된다.
	ORDER BY U.userId;

 

이렇게 하면 서로 테이블과 일치한 행과 조인되고 또한 유저테이블에 있는 데이터도 모두 가져온다.

 

유저 테이블에는 데이터가 있지만 구매 테이블에는 없는 데이터의 경우에는 구매 테이블 B 테이블의 열의 값이 NULL로 처리된다.

right join

left 조인의 반대로 **"오른쪽 테이블에 있는 데이터는 모두 가져와라."**라는 말이다.

 

예시 코드는 아래를 참고하자.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		RIGHT JOIN buytbl B
			ON U.userId = B.userId
	ORDER BY U.userId;

 

이것은 이제부터 유저테이블이 기준이 아닌 구매 테이블이 기준이 되어서 데이터가 처리될것이다.

full join

많이 쓰지는 않지만 알아두자.

 

쉽게 말해서 left join과 right 조인이 합쳐진 경우이다. full join은 특별한 구문이 없다.

 

아래를 참고하자.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		LEFT JOIN buytbl B
			ON U.userId = B.userId -- 이 조건이 걸리지 않으면 NULL로 처리된다.
UNION
SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		RIGHT JOIN buytbl B
			ON U.userId = B.userId;

 

UNION은 두 테이블의 결과를 서로 합쳐준것이다. 두 테이블의 각 열의 데이터 타입과 열의 개수가 모두 같아야지만 UNION이 정상적으로 작동 할 수 있다.

Cross join

특별한 구문은 없다. 이것은 하나의 테이블이 메인테이블이 되어서 메인테이블의 1개의 행당 두번째 테이블의 모든 데이터가 합쳐져서 출력되는 것이다.

 

이건 많이 쓰지는 않는다. 위와 같이 하나의 row당 2번째 테이블의 전부의 row가 출력되는 것이다.

 

코드는 아래를 참고하자.

SELECT * FROM buytbl CROSS JOIN usertbl;

 

위의 코드와 같이 결과값이 출력된다, cross join의 용도는 대용량의 테스트 데이터베이스를 생성할때 많이 쓰인다.

Self Join

자기 자신을 조인한다는 의미이다.

 

하나의 예시를 들자면 직원 테이블에는 직원 이름과 직속상관과 연락번호라는 열이 존재한다.

 

만약 어떤 사원의 이름과 직속상관의 이름과 연락번호를 한꺼번에 알고싶다면 자기 자신을 조인해야 될것이다. 아래의 코드처럼 하면된다.

SELECT E.name, E.manage, S.number
	FROM employestbl E
		INNER JOIN employees S
			ON S.name = E.manage
	ORDER BY E.name;

 

이렇게 하면 검색조건에서 직속상관의 이름과 일치하는 열을 서로 조인하는 것을 알 수 있다.

이상으로 조인의 종류에 대하여 알아봤다.

 

left, right, full, inner join같은 경우에는 join은 모두 집합이다. 집합의 밴다이어그램으로 이해하면 이해가 더욱더 빠르게 될것이다.

https://t1.daumcdn.net/cfile/tistory/2623B333576CC00037

<이것이 MYSQL이다> 7장 중후반 정리 (pivotTable, Join)

7장 주요내용

mysql 7장에서는 대표적으로 sql프로그래밍, join, pivot에 대하여 배우게 되었다.

sql 고급문을 다룬 내용이므로 이해하기 어려운 부분이 있었지만 잘 소화하였던것 같다.

특히 join이라는 개념은 mysql과 다른 관계형 데이터베이스에서 무지막지 하게 많이 쓰이는 개념이므로 정말 중요하다.

Pivot Table (피벗 테이블)

피벗테이블은 데이터의 통계나 집계를 하기 위하여 많이 쓰이는 형식이다.

간단하게 쇼핑몰의 구매 테이블을 생각하게 된다면 한 유저는 여러 물건을 구입할수 있게 된다.

아래와 같은 테이블이 될것이다.

데이터 테이블 예시 (prodTbl)

위와 같이 같은 유저 아이디로 여러개의 물건을 구입할 수 있다.

하지만 이러한 통계는 눈에 잘 보이지 않는다.

이제 이걸 pivot table로 나타내보자.

SELECT id,
	SUM(IF(prod="냉장고", cnt, 0)) AS '냉장고', -- mysql에서 if문 처리는 삼항연산자와 비슷하다.
	SUM(IF(prod="에어컨", cnt, 0)) AS '에어컨',
	SUM(IF(prod="선풍기", cnt, 0)) AS '선풍기',
	SUM(prod) AS '합계'
		FROM prodTbl 
		GROUP BY id;

집계함수와 조건문을 이용해서 pivot table을 만들 수 있다.

이 명령어를 실행하면 아래와 같은 데이터가 나오게 된다.

prodtbl pivot 결과 테이블

집계함수와 조건문을 이용하면 중복되는 열의 값들을 순환해서 해당 열들을 여러 열로 반환하여 집계하는 테이블을 피벗 테이블이라고 할 수 있다.

Inner Join

정말 많이 쓰이는 조인방식이다 mysql에서는 일반적으로 join이라고만 해도 inner join이 된다.

예를 들어서 A테이블과 B테이블이 있으면 두 테이블 사이의 같은 값을 묶어서 조인하는 방식이다.

유저 테이블과 구매 테이블은 서로 유저 아이디로 관계가 있다.

조인에서는 ON이라는 조인 조건문을 이용하여 서로의 테이블과 연관되는 ROWS를 비교한다.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userId = B.userId
	ORDER BY U.userId;

일반적인 Inner join 방식이다. U와 B는 각각 테이블의 별칭이다.

별칭이란 각각의 테이블 이름을 붙여서 지정하면 코드의 길이도 늘어나고 이해하기가 힘들것이다.

따라서 각각의 테이블에 특별한 별칭을 붙이고 그 별칭으로 열을 가져온다.

코드도 엄청나게 간결해지고 이해하기도 쉽다. 정말 많이 쓰이므로 별칭은 잘 기억하자.

ON을 이용하여서 buytbl, usertbl 모두 서로의 아이디가 일치한 rows가 존재하는 데이터만 가져온다.

만약 유저 테이블에는 데이터가 있는데 구매 테이블에는 해당 유저 아이디와 일치한 row가 없으면 조인되지 않는 방식이 Inner Join이다.

만약 3개의 테이블로 N:M관계 조인을 하고 싶은 경우에는 아래와 같이 하면된다.

SELECT U.userId, B.prod, A.adminName, U.addr
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userId = B.userId
		Inner JOIN admintbl A
			ON B.number = A.number
	ORDER BY U.userId;

N:M 조인을 할 겅우에는 2개의 테이블을 엮기 위해서 첫번째로 유저 테이블과 구매 테이블로 조인하고 구메 테이블과 관리 테이블을 조인하여 관계를 설정해주어야 한다.

Outer Join

outer join은 평균적으로 많이 쓰이지는 읺는다. 하지만 알아두면 좋으므로 알아두자.

outer join은 크게 3가지로 분류할 수 있다. left join, right join, full join이 있다.

left join

왼쪽 테이블을 기준으로 **"왼쪽 테이블에 있는 데이터는 모두 가져와라."**라는 말과 같다.

Inner join에서는 두 테이블과 조건이 일치한 행만 조인됬다면 이번에는 왼쪽에 있는 테이블의 값은 모두 가져온다. 코드 예시는 아래와 같다.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		LEFT JOIN buytbl B
			ON U.userId = B.userId -- 이 조건이 걸리지 않으면 NULL로 처리된다.
	ORDER BY U.userId;

이렇게 하면 서로 테이블과 일치한 행과 조인되고 또한 유저테이블에 있는 데이터도 모두 가져온다.

유저 테이블에는 데이터가 있지만 구매 테이블에는 없는 데이터의 경우에는 구매 테이블 B 테이블의 열의 값이 NULL로 처리된다.

right join

left 조인의 반대로 **"오른쪽 테이블에 있는 데이터는 모두 가져와라."**라는 말이다.

예시 코드는 아래를 참고하자.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		RIGHT JOIN buytbl B
			ON U.userId = B.userId
	ORDER BY U.userId;

이것은 이제부터 유저테이블이 기준이 아닌 구매 테이블이 기준이 되어서 데이터가 처리될것이다.

full join

많이 쓰지는 않지만 알아두자.

쉽게 말해서 left join과 right 조인이 합쳐진 경우이다. full join은 특별한 구문이 없다.

아래를 참고하자.

SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		LEFT JOIN buytbl B
			ON U.userId = B.userId -- 이 조건이 걸리지 않으면 NULL로 처리된다.
UNION
SELECT U.userId, U.addr, B.prod, B.price,B.prodDate
	FROM usertbl U
		RIGHT JOIN buytbl B
			ON U.userId = B.userId;

UNION은 두 테이블의 결과를 서로 합쳐준것이다. 두 테이블의 각 열의 데이터 타입과 열의 개수가 모두 같아야지만 UNION이 정상적으로 작동 할 수 있다.

Cross join

특별한 구문은 없다. 이것은 하나의 테이블이 메인테이블이 되어서 메인테이블의 1개의 행당 두번째 테이블의 모든 데이터가 합쳐져서 출력되는 것이다.

이건 많이 쓰지는 않는다. 위와 같이 하나의 row당 2번째 테이블의 전부의 row가 출력되는 것이다.

코드는 아래를 참고하자.

SELECT * FROM buytbl CROSS JOIN usertbl;

위의 코드와 같이 결과값이 출력된다, cross join의 용도는 대용량의 테스트 데이터베이스를 생성할때 많이 쓰인다.

Self Join

자기 자신을 조인한다는 의미이다.

하나의 예시를 들자면 직원 테이블에는 직원 이름과 직속상관과 연락번호라는 열이 존재한다.

만약 어떤 사원의 이름과 직속상관의 이름과 연락번호를 한꺼번에 알고싶다면 자기 자신을 조인해야 될것이다. 아래의 코드처럼 하면된다.

SELECT E.name, E.manage, S.number
	FROM employestbl E
		INNER JOIN employees S
			ON S.name = E.manage
	ORDER BY E.name;

 

이렇게 하면 검색조건에서 직속상관의 이름과 일치하는 열을 서로 조인하는 것을 알 수 있다.

 

이상으로 조인의 종류에 대하여 알아봤다

.

left, right, full, inner join같은 경우에는 join은 모두 집합이다. 집합의 밴다이어그램으로 이해하면 이해가 더욱더 빠르게 될것이다.