복수의 테이블 다루기 (join)

테이블 집합 연산에서는 세로(행) 방향으로 데이터가 늘어나거나 줄어드는 계산을 했다.

• join을 통한 결합에서는 가로(열) 방향으로 데이터가 늘어나는 계산이 된다.

보통 데이터베이스는 하나의 테이블에 많은 데이터를 저장하지 않고 몇 개의 테이블로 나누어 저장한다.

• 여러 개로 나뉜 데이터를 하나로 묶어 결과를 내는 방법이 테이블 결합이다.
• 결합을 이해하는 동시에 기본이 되는 개념이 집합론의 ‘곱집합’이다.

---

곱집합과 교차결합

곱집합은 합집합이나 교집합처럼 집합의 연산 방법 중 하나이다.

• 두 개의 집합을 곱하는 연산 방법으로 ‘적집합’ 또는 ‘카티전곱(Cartesian product)’라고 불린다.

집합 X 는 {A, B, C} 집합 Y는 {1, 2, 3}이라는 세 개의 요소를 가진다.

집합 X와 Y의 곱집합을 구하면 집합 X의 요소 A에 집합 Y의 각 요소를 붙여 계산하는 것이다.

• (A,1)을 하나의 요소라고 생각해야 한다.

예를들어 한 시즌에 6화까지 있는 드라마가 3시즌까지 반영되는 경우

• {1, 2, 3}의 집합과 {1, 2, 3, 4, 5, 6}의 집합을 곱집합으로 계산하면 된다.

시즌1	1-1화	1-2화	1-3화	1-4화	1-5화	1-6화
시즌2	2-1화	2-2화	2-3화	2-4화	2-5화	2-6화
시즌3	3-1화	3-2화	3-3화	3-4화	3-5화	3-6화

 

교차집합(Cross Join)

데이터베이스의 테이블은 집합의 한 종류라고 말할 수 있다.

SELECT * FROM 테이블명1, 테이블명2

• 테이블을 두 개 지정하면 이들은 곱집합으로 계산된다.
• 세 개, 네 개로도 지정이 가능하다.
  • 테이블 수가 많아지면 조합 수가 엄청나게 늘어나 집합이 거대해진다.
  • 이렇게 많은 테이블을 교차결합하는 경우는 드물고 결합 방법으로는 교차결합보다 내부결합이 자주 사용된다.

교차결합을 하기 위해서는 FROM 구에서 쉼표(,)로 구분하여 두 테이블을 지정한다.

#FROM구에 테이블 두 개를 지정해 곱집합 구하기
SELECT * FROM sample_x,sample_y;

x	y
A	1
B	1
C	1
A	2
B	2
C	2
A	3
B	3
C	3

교차결합은 두 개의 테이블을 곱집합으로 계산한다.

• FROM 구에 복수의 테이블을 지정하면 교차결합을 한다.

UNION 연결과 결합 연결의 차이

1. UNION에서도 집합을 더해 새로 큰 집합을 만들어 계산할 수 있었다.
2. FROM 구에서 복수의 테이블을 결합할 때도 새로 큰 집합을 만들어 계산한다.

두 가지 방식이 서로 비슷하지만 확대 방향이 다르다.

• UNION으로 합집합을 구했을 경우에는 세로 방향으로 더해진다.
• FROM 구로 테이블을 결합할 경우에는 가로 방향으로 더해지게 된다.
  • 결합은 열(가로)방향으로 확대된다.

---

내부 결합

수학에서의 집합은 유일한 요소로 구성된다.

• 중복된 값이 존재하지 않는다.

관계형 데이터베이스에서도 테이블의 데이터가 유일한 값을 가지도록 권장한다.

• 간단히 말하면 기본키를 가지도록 하는 게 좋다.

데이터베이스에는 다양한 데이터가 저장되지만 동일한 데이터를 중복해서 여러 곳에 저장하지 않도록 하는 편이 좋다.

• 데이터가 변경되는 경우 흩어져 저장되어 있는 데이터를 모두 동일한 값으로로 변경하기 힘든 일이기 때문
  • 이 때 기본키는 하나의 데이터행을 대표할 수 있는 속성을 가진다.

상품의 가격이나 이름과 같은 데이터를 저장하는 ‘상품 테이블’을 작성할 때

• 상품의 속성으로 상품명, 메이커명, 가격, 상품분류 등을 꼽을 수 있다.
• 상품명을 기본키로 사용하면 중복할 우려가 있으므로 기본키로는 적합하지 않는다.
  • 이러한 이유로 ‘상품코드’를 ‘기본키’로 사용하는 경우가 많다.

CREATE TBALE 상품 (
	상품코드 CHAR(4) NOT NULL, #기본키로 지정하기 위해서 NOT NULL
	상품명 VARCHAR(30),
	메이커명 VARCHAR(30),
	가격 INTEGER,
	상품분류 VARCHAR(30),
	PRIMARY KEY(상품코드)
);

• 한글을 사용하여 테이블과 열의 이름을 정의할 경우에는 이름을 **백쿼트(`)**로 묶어줘야 된다.
  • MAC에서 option + ₩

상품코드	상품명	메이커명	가격	상품분류
0001	상품OO	OO메이커	100	식료품
0002	상품XX	XX메이커	200	식료품
0003	상품AA	AA메이커	1200	생활용품

이처럼 테이블을 만들게 되면 동일한 상품명을 가진 상품이라도 구별하여 등록할 수 있다.

• 전자상거래 시스템에서 상품 테이블 하나만으로 운영하는 곳은 없다.
  • 상품 데이터를 참조하는 별도의 테이블이 존재한다.
  • 재고도 같이 관리하는 경우에는 입출고나 재고 수를 상품단위로 관리하는 테이블이 있을 것이다.

재고관리 테이블

CREATE TABLE 재고수(
	상품코드 CHAR(4),
	입고날짜 DATE,
	재고수 INTEGER
);

• 재고수 테이블을 참조하는 다른 테이블을 위해 기본키를 지정해두는 것도 좋다.

상품코드	입고날짜	재고수
0001	2023-02-21	200
0002	2023-02-10	500
0003	2023-02-02	10

• 실제 시스템에서는 더 많은 열로 구성되어 있을 것이다.

재고수 테이블에서의 착안점은 상품코드를 통해 상품 테이블과 연결할 수 있다는 것이다.

• 상품 테이블의 기본키는 ‘상품코드’이다.
  • 이 열의 값을 알면 상품명을 포함한 상품 데이터를 참조할 수 있다.
• 다른 테이블의 데이터를 참조해야 하는 경우
  • 참조할 테이블의 기본키와 동일한 이름과 자료형으로 열을 만들어 행을 연결하는 경우가 많다.

재고수 테이블에서 상품분류가 식료품인 상품의 재고수를 표시하는 경우를 생각해보자

• 이 경우 상품코드보다 상품명으로 표시하면 알아보기 쉬울 것이다.

상품명	재고수
상품OO	200
상품XX	500

재고수는 재고수 테이블에서 가져오면 되지만 상품명과 상품분류는 상품 테이블에 있다.

• 재고수 테이블과 상품 테이블을 결합해 가로로 나열하고자 한다.
  • FROM 구에서 테이블을 서로로 결합해야 한다.

SELECT * FROM 상품, 재고수;

FROM 구에 테이블을 복수로 지정하면 곱집합으로로 계산된다.

• 상품 테이블 행에 재고수 데이터 행으로 곱집합을 구하면 행은 3 * 3 = 9 가 된다.

상품코드	상품명	메이커명	가격	상품분류	상품코드	재고수
0001	상품OO	OO메이커	100	식료품	0001	200
0002	상품XX	XX메이커	200	식료품	0001	200
0003	상품AA	AA메이커	1200	생활용품	0001	200
0001	상품OO	OO메이커	100	식료품	0002	500
0002	상품XX	XX메이커	200	식료품	0002	500
0003	상품AA	AA메이커	1200	생활용품	0002	500
0001	상품OO	OO메이커	100	식료품	0003	10
0002	상품XX	XX메이커	200	식료품	0003	10
0003	상품AA	AA메이커	1200	생활용품	0003	10

• 왼쪽이 상품 테이블의 데이터, 오른쪽이 재고수 테이블의 데이터이다.

만들어진 집합에서 원하는 데이터를 검색하기 위해서는 WHERE 구로 조건을 지정합니다.

• 상품코드가 같다는 조건이 필요하다.
• 열 이름이 ‘상품코드’로 서로 동일하므로 WHERE 구에 조건식을 지정할 때 테이블 이름도 같이 지정할 필요가가 있다.
  • 상품 테이블의 상품코드 열은 ‘상품.상품코드’ , 재고수 테이블의 상품코드 열은 ‘재고수.상품코드’로 지정한다.

SELECT * FROM 상품, 재고수
	WHERE 상품.상품코드 = 재고수.상품코드;

상품코드	상품명	메이커명	가격	상품분류	상품코드	재고수
0001	상품OO	OO메이커	100	식료품	0001	200
0002	상품XX	XX메이커	200	식료품	0002	500
0003	상품AA	AA메이커	1200	생활용품	0003	10

이렇게 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 ‘내부결합(Inner Join)’이라 부른다.

• 결합 조건으로 보면 등결합 이라고도 부를 수 있다.

상품코드가 ‘식료품’이라는 조건이 필요하니 WHERE 구에 추가해야 보자

• 추가할 때는 기존 조건식과 상품분류의 조건식이 모두 참이여야 하므로 AND로 조건식을 연결해야 한다.

SELECT 상품.상품명, 재고수.재고수 FROM 상품, 재고수
	WHERE 상품.상품코드 = 재고수.상품코드;
		AND 상품.상품분류 = '식료품';

상품명	재고수
상품OO	200
상품XX	500

• 이처럼 원하는 결과를 얻을 수 있다.

WHERE 구에는 두 개의 조건식이 지정되어 있다.

• 첫 번째 조건식은 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것
  • 이 조건식의 조건을 ‘결합조건’이라 부른다.
• 두 번째 조건식은 결합 조건이 아닌 검색 조건이다.

---

INNER JOIN으로 내부결합하기

결합방법에 관해서 간단히 정리해보자

FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다.
교차결합을 하면 곱집합으로 계산된다.
WHERE 조건을 지정해 곱집합에서 필요한 조건만 검색할 수 있다.

• 이와 같은 결합방법을 상품 테이블과 재고수 테이블로 확인해 보았다.
  • 지금까지 설명한 결합방법은 구식이다.

최근에는 INNER JOIN 키워드를 사용한 결합방법이 일반적으로 통용된다.

• 상품 테이블과 재고수 테이블을 이용한 사례를 INNER JOIN을 활용해 바꿔보자

SELECT 상품.상품명, 재고수.재고수 
	FROM 상품 INNER JOIN 재고수
		ON 상품.상품코드 = 재고수.상품코드
	WHERE 상품.상품분류 = '식료품';

#내부결합
SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

구식 방법에서는 쉼표(,)로 구분하여 테이블을 FROM 구에 지정했다.

• 새로운 형식에서는 테이블과 테이블 사이에 ‘INNER JOIN’이라는 키워드를 넣는다.
  • INNER는 ‘안쪽’이라는 의미이며 ‘JOIN’은 ‘연결시킨다’라는 의미로 ‘내부결합’이라는 의미가 된다.
• 구식 방법에서는 WHERE 구에 결합조건을 지정하였지만 INNER JOIN에서는 ON을 사용하여 결합조건을 지정한다.

INNER JOIN으로 두 개 테이블을 가로로 결합할 수 있다.

---

내부결합을 활용한 데이터 관리

‘하나의 데이터는 한 군데에 저장한다’라는 룰에 따라 데이터 구조를 설계한다고 했을 때

• 메이커코드와 메이커명을 가지는 메이커 테이블을 작성해 데이터를 관리해보자

상품을 제조하는 메이커가 많이 있다고 해도 상품 수보다는 적을것이다.

• 코드와 이름을 가지는 테이블로 분할해 관리하면 저장공간도 절약할 수 있다.

메이커코드는 메이커를 나타내는 코드라는 의미에서 맨 앞 자를 ‘M’으로로 하였습니다.

• 여기에는 두 개의 메이커가 저장되어 있다.

CREATE TABLE 메이커 (
	메이커코드 CHAR(4) NOT NULL,
	메이커명 VARCHAR(30),
	PRIMARY KEY (메이커코드)
);

메이커코드	메이커명
M001	OO메이커
M002	XX메이커

• 상품 테이블에서는 메이커명을 메이커코드로 변경해 둔다.

상품코드	상품명	메이커명	가격	상품분류
0001	상품OO	M001	100	식료품
0002	상품XX	M001	200	식료품
0003	상품AA	M002	1200	생활용품

• ‘상품코드’와 ‘상품명’처럼 ‘OO코드’와 ‘OO명’의 조합으로 열을 지정하고 ‘OO코드’가 기본키가 되는 패턴은 자주 사용된다.
• 현재 두 개의 테이블로 분활되어 있지만 상품명과 메이커명을 같이 출력하고 싶을 때는 내부결합을 사용한다.

SELECT S.상품명,M.메이커명
	FROM 상품 S INNER JOIN 메이커 M
		ON S.메이커코드 = M.메이커코드;

상품명	메이커명
상품OO	OO메이커
상품XX	OO메이커
상품AA	AA메이커

• SELECT 명령에서 복수의 테이블을 다룰 경우 어느 테이블의 열인지 정확하게 지정해야 한다.
  • 테이블명을 매번 지정하는 것은 번거롭기 떄문에 짧게 줄여 별명을 붙이는 경우가 많다.
    • 메이커 테이블에는 ‘M’ , 상품 테이블에는 ‘S’라는 짧은 별명을 붙였다.

상품 테이블의 메이커코드만을 살펴보면 중복하는 행이 있다. (실무에서 가끔 있는 내용)

• ‘상품OO와 상품XX는 같은 OO메이커가 제조한 상품’인 경우에 해당한다.
  • 단, 기본키 제약은 상품코드에만 적용되어 있어 데이터상으로도 제약에 위반되지 않는다.
• 반대로 메이커 테이블은 메이커코드에 기본키가 지정되어 있기 때문에 중복을 허용하지 않는다.
  • 메이커코드는 유일하게 하나씩만 존재하며 중복될 수 없다.
    • 존재하지 않거나 혹은 하나만 존재한다.

• 결합이나 데이터베이스의 테이블 설계를 이해하는 동시에에 핵심이 되는 개념이다.

A 테이블과 B 테이블을 결합했을 때, A와 B 중 어느 쪽이 하나의 행만 가지는지(일대다, 다대일) 아니면 양쪽 모두 하나의 행을 가지는지(일대일) 등과 같은 ‘서로 결합하는 테이블 간의 관계’가 중요하다.

 

외부키

메이커 테이블의 메이커코드는 기본키이다.

• 그에 비해 상품 테이블의 메이커코드는 ‘외부키’라 불리는 것으로 다른 테이블의 기본키를 참조하는 열이 외부키가 된다.

자기결합(Self Join)

자기결합은 테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것을 말한다.

• 특별히 명령어가 정해저 있는 것은 아니다.

SELECT S1.상품명, S2.상품명
	FROM 상품 S1 INNER JOIN 상품 S2
		ON S1.상품코드 = S2.상품코드;

상품명	상품명
상품OO	상품OO
상품XX	상품XX
상품AA	상품AA

상품 테이블을 가로로 두 개 나열해 상품코드로 결합했다.

• 자기결합에서는 결합의 좌우가 같은 테이블이 되기 때문에 이를 구별하기 위해서는 반드시 별명을 붙여야 한다.
• 어떻게 생각하면 쓸모 없는 쿼리일 수 있다.
  • 자기결합은 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우에 자주 사용된다.

---

외부결합

결합 방법은 크게 ‘내부결합’과 ‘외부결합’의 두 가지로 구분된다.

외부결합이라고 해도 교차결합으로 결합 조건을 지정하여 검색하는 기본적인 사고 방식은 같다.

• 외부결합은 ‘어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지’를 변경할 수 있는 결합 방법이다.

상품 테이블과 재고수 테이블 중에 상품 테이블에만 행이 존재하는 상황을 생각해 보자

• 실제로 상품 데이터를 등록한 직후

상품코드	상품명	메이커코드	가격	상품분류
0001	상품OO	M001	100	식료품
0002	상품XX	M001	200	식료품
0003	상품AA	M002	1200	생활용품
0009	추가상품	M001	300	식료품

• 상품코드가 0009인 행을 새롭게 추가했다.
  • 재고수 테이블에는 아직 이 상품에 대한 데이터가 없다.
  • 곱집합을 구해도 0009 = 0009가 되는 행이 없으므로 내부결합 결과에서는 상품코드가 0009인 상품은 제외된다.

SELECT 상품3.상품명,재고수.재고수
FROM 상품3 INNER JOIN 재고수
ON 상품3.상품코드 = 재고수.상품코드
WHERE 상품3.상품분류 = '식료품';

상품명	재고수
상품OO	200
상품XX	500

이런 경우에 외부결합을 사용하면 된다.

• 외부결합은 결합하는 테이블 중에 어느 쪽을 기준으로 할지 결정할 수 있다.
• 상품테이블 (INNET JOIN 왼쪽)을 기준으로 INNER JOIN 대신 LEFT JOIN을 사용해보자

SELECT 상품3.상품명,재고수.재고수
	FROM 상품3 LEFT JOIN 재고수
		ON 상품3.상품코드 = 재고수.상품코드
	WHERE 상품3.상품분류 = '식료품';

상품명	재고수
상품OO	200
상품XX	500
추가상품	NULL

재고수 테이블에는 0009에 대한 데이터가 없으므로 값이 NULL로 표시되는 점에 주의해야 된다.

• 기준이 되는 상품 테이블을 JOIN의 왼쪽에 기술했으므로 LEFT JOIN이라 지정했다.
• 반대의 경우에는 RIGHT JOIN을 사용해 외부결합을 시행하면 된다.

LEFT JOIN, RIGHT JOIN으로 외부결합을 할 수 있다!

추가적으로 CROSS JOIN으로 교차결합을 하면 곱집합을 구할 수 있다.

#SELECT * FROM A, B
SELECT * FROM A CROSS JOIN B

 

구식방법에서의 외부결합과 표준 SQL

 

구식 결합방법은 사용하지 않지만 이런게 구식이다라는 것을 알기위해 적어본다.

MySQL은 비교적 최근에 나온 DB이다.

• 구식 방법을 이용해도 내부결합은 가능하나 외부결합은 할 수 없다.

Oracle로 예를 들어보자

구식 결합방법에서 FROM 구에 결합 조건을 기술하지 않는다.

• 대신 WHERE 구로 결합 조건을 지정한다.
  • 그냥 조건신을 지정하면 내부결합이 되어버리므로, 외부결합으로 진행하고 싶은 경우에는 특별한 연산자를 사용해야 한다.
• Oracle에서는 데이터가 존재하지 않을 수도 있는 테이블의 열에 (+)라는 특수한 기호를 붙여서 조건을 지정한다.

SELECT 상품3.상품명, 재고수.재고수
	FROM 상품3,재고수
	WHERE 상품3.상품코드 = 재고수.상품코드 (+)
		AND 상품3.상품분류 = '식료퓸';

• 그 밖에도 SQL Server에서는 특수한 연산자 (=또는=)를 사용해서 외부결합을 할 수 있다.

이전에는 이처럼 데이터베이스에 따라 서로 다른 방법으로 외부결합을 지정했다.

• 현재는 표준화로 인해 내부결합은 INNER JOIN, 외부결합은 LEFT JOIN이나 RIGHT JOIN을 사용하도록 권장한다.

표준화가 진행된 현재에는 별다른 장정이 없는 구식 결합방법은 사용하지 않는다.