[55] 230317 SQL Ch. 7 복수의 테이블 다루기: 31강 집합 연산, 32강 테이블 결합, 33강 관계형 모델 [K-디지털 트레이닝 54일]

230317 Fri 55th class

Ch 7. 복수의 테이블 다루기
진도: p.  274 ~ (교재: SQL 첫걸음, 저자: 아사이 아츠시)
MySQL Workbench Manual: https://dev.mysql.com/doc/workbench/en/
MySQL 튜토리얼: https://www.tutorialspoint.com/mysql/
SQLD 요약 pdf: https://yurimac.tistory.com/40
책 요약 (9p) pdf: https://brunch.co.kr/@ydy702/10

SQL 첫걸음 | 아사이 아츠시 - 교보문고

공부한 것 중 기억할 것을 정리했다.

---

요약
FROM JOIN 절 형태

FROM JOIN  절 형태	설명
교차 결합 CROSS JOIN	카티시안 곱 양쪽 집합의 M*N건의 데이터 조합이 발생한다.
내부 결합 INNER JOIN	JOIN 조건에서 동일한 값이 있는 행만 반환, USING이나 ON 절을 필수적으로 사용 -- 내부결합(inner join키워드를 이용, 이때 inner는 생략할 수 있음.) select 상품명, 재고수 -- from 상품 inner join 재고수 from 상품 join 재고수 on 상품.상품코드 = 재고수.상품코드;
외부 결합 OUTER JOIN	JOIN 조건에서 동일한 값이 없는 행도 반환 가능하다, USING 이나 ON 조건절 반드시 사용해야 함. SQL 식에서 (+) 안 붙은 쪽으로 JOIN한다.
	LEFT OUTER JOIN: A에만 있고 B는 NULL 인 값 포함 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다
	RIGHT OUTER JOIN: A에 없고 B에만 있는 값 포함 LEFT OUTER JOIN의 반대
	FULL OUTER JOIN: 좌우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 중복 데이터는 삭제한다.
NATURAL JOIN	두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN(등가 조인)수행,  NATURAL JOIN이 명시되면 추가로 USING, ON,  WHERE 절에서JOIN 조건을 정의할 수 없다, SQL Sever는 X
USING 조건절	같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN(등가 조인)을 할 수 있다. JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다 , SQL Server 지원x
ON 조건절	ON 조건절과 WHERE 조건절을 분리하여 이해가 쉬우며 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다. ALIAS나 테이블명 반드시 사용

참고: https://keep-cool.tistory.com/41

---

Ch. 7 복수의 테이블 다루기

책의 목차
__31강__집합 연산
1. SQL과 집합
2. UNION으로 합집합 구하기
3. 교집합과 차집합

__32강__테이블 결합
1. 곱집합과 교차결합
2. 내부결합
3. INNER JOIN으로 내부 결합하기
4. 내부결합을 활용한 데이터 관리
5. 외부결합

__33강__관계형 모델
1. 관계형 모델
2. 관계형 모델과 SQL 

31강 집합 연산 p. 274

'복수의 테이블'을 사용해 데이터를 검색하는 방법을 알아본다.
관계형 모델을 채택한 데이터베이스를 관계형 데이터베이스라고 한다. 

---

31.1 SQL과 집합 p. 275

데이터베이스에서는 테이블의 행이 요소에 해당한다. 
하나의 행이 곧 하나의 요소가 된다.
SELECT 명령으로 반환된 결과 전체를 하나의 집합이라고 생각하면 된다. 

---

31.2 UNION으로 합집합 구하기

- UNION
합집합을 계산할 경우에는 UNION 키워드를 사용한다. 

예제 7-1) UNION으로 합집합 구하기

SELECT * FROM sample71_a;

SELECT * FROM sample71_b;

예제 7-2) 두 개의 SELECT 명령을 UNION으로 합집합 구하기
중복된 데이터는 하나로 취급한다. 

SELECT * FROM sample71_a
UNION -- 두 개의 명령을 하나의 명령으로 합친다.
SELECT * FROM sample71_b;

Point: UNION으로 두 개의 SELECT 명령을 하나로 연계해 질의(Query) 결과를 얻을 수 있다.

---

완전히 열 구성이 다른 테이블을 UNION으로 묶을 수는 없다.
(같은 칸으로 이루어져야 한다. 열의 개수가 맞아야 한다.) 
열을 따로 지정하여 각 SELECT 명령에서 집합의 요소가 될 데이터를 서로 맞춰주면 UNION으로 실행할 수 있는 쿼리가 된다.

테이블 sample31

SELECT a FROM sample71_a
UNION
SELECT b FROM sample71_b
UNION
SELECT age FROM sample31;

테이블 sample31의 age

테이블 sample31의 name

UNION으로 묶을 때 결과가 모두 같지만 결괏값의 나열 순서는 달라질 수도 있다. 

SELECT * FROM sample71_a UNION SELECT * FROM sample71_b;
SELECT * FROM sample71_b UNION SELECT * FROM sample71_a;

결괏값의 나열 순서가 다르다.

---

1) UNION을 사용할 때의 ORDER BY
합집합의 결과를 정렬하므로 가장 마지막의 SELECT 명령에 ORDER BY를 지정해야 한다. 

-- 마지막의 SELECT 명령에 ORDER BY를 지정한다.
SELECT a AS c FROM sample71_a
UNION
SELECT b AS c FROM sample71_b ORDER BY c;

별명으로 열 이름을 서로 일치시킨다.

두 개의 SELECT 명령에서 열 이름이 서로 일치해야 문제가 없으므로 서로 동일하게 별명을 붙여서 정렬할 수 있다.

Point: UNION으로 SELECT 명령을 연결하는 경우, 가장 마지막 SELECT 명령에 대해서만 ORDER BY 구를 지정할 수 있다. 
ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다. 

---

2) UNION ALL

예제 7-3) 두 개의 SELECT 명령에 UNION ALL을 적용해 합집합 구하기
중복된 데이터도 포함해서 출력한다. 

SELECT * FROM sample71_a
UNION ALL
SELECT * FROM sample71_b;

---

31.3 교집합(INTERSECT)과 차집합(EXCEPT) p. 282

MySQL에서는 지원하지 않는다.
교집합(두 개의 집합이 겹치는 부분): INTERSECT
차집합(집합에서 다른 쪽의 집합을 제거하고 남은 부분): EXCEPT (Oracle: MiNUS)
를 사용한다.

공집합(원소가 하나도 없는 집합): 완전히 같은 집합끼리 차집합을 계산하면 아무런 요소도 존재하지 않는다.

---

32강 테이블 결합 (join) p. 284

RDBMS(Relational database management system)에서 대단히 중요한 개념이다. 

---

32.1 곱집합과 교차결합

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

1) 교차결합(Cross Join)

교차 결합

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

예제 7-4) sample72_x와 sample72_y

SELECT * FROM sample72_x;
SELECT * FROM sample72_y;

예제 7-5) FROM 구로 곱집합 구하기 

-- FROM구에 테이블 두 개를 지정해 곱집합 구하기
SELECT * FROM sample72_x, sample72_y;

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

---

2) UNION 연결과 결합 연결의 차이
Point: 결합은 열(가로 =>)방향으로 확대된다.

---

32.2 내부결합

INNER JOIN은 ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만을 가져옵니다.

문법
1. 첫번째테이블이름
INNER JOIN 두번째테이블이름
ON 조건

2. 첫번째테이블이름
JOIN 두번째테이블이름
ON 조건

ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있습니다.
표준 SQL과는 달리 MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 모두 같은 의미로 사용됩니다.

예제)

SELECT *
FROM Reservation
INNER JOIN Customer
ON Reservation.Name = Customer.Name;

SELECT *
FROM Reservation
JOIN Customer
ON Reservation.Name = Customer.Name;

INNER JOIN의 경우에는 앞서 살펴본 표준 SQL 방식과는 별도로 MySQL에서만 사용할 수 있는 방식이 따로 존재합니다. 다음 예제는 앞서 살펴본 INNER JOIN 예제와 같은 실행 결과를 보여줍니다.
결과) 

위 예제의 실행 결과처럼 JOIN의 결과는 하나의 테이블 형태로 반환됩니다.
MySQL 수업 예제에서 사용되는 Reservation 테이블과 Customer 테이블은 다음과 같습니다.

Reservation 테이블

Customer 테이블

INNER JOIN의 결과를 벤 다이어그램으로 나타내면 다음과 같습니다.

 

예제)

SELECT *
FROM Reservation, Customer
WHERE Reservation.Name = Customer.Name;

위의 예제처럼 테이블의 이름이 길거나 복잡한 경우에는 별칭(alias)을 사용하여 SQL 구문을 간략화할 수 있습니다. 다음 예제는 앞의 예제를 별칭(alias)을 사용하여 간략화한 예제입니다.
예제)

SELECT *
FROM Reservation AS r, Customer AS c
WHERE r.Name = c.Name;

참고: http://www.tcpschool.com/mysql/mysql_multipleTable_join

---

예제 7-6) 상품 테이블 작성하기

CREATE TABLE 상품(
    상품코드 CHAR(4) NOT NULL,
    상품명 VARCHAR(30),
    메이커명 VARCHAR(30),
    가격 INTEGER,
    상품분류 VARCHAR(30),
    PRIMARY KEY (상품코드)
 );

---

예제 7-7) 재고수 테이블 작성하기 

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

---

예제 7-8) 상품 테이블과 재고수 테이블을 교차결합하기

SELECT * FROM 상품, 재고수;

상품 테이블, 재고수 테이블

---

예제 7-9) 상품코드가 같은 행을 검색하기

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

---

예제 7-10) 검색할 행과 반환할 열 제한하기

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

---

32.3 INNER JOIN으로 내부 결합하기 p. 294

SELECT 상품.상품명, 재고수.재고수
  FROM 상품(테이블명1) INNER JOIN 재고수(테이블명2)
    ON 상품.상품코드 = 재고수.상품코드 (결합조건)
  WHERE 상품.상품분류 = '식료품';

내부결합

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

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

두 코드의 결과는 같다.

두 테이블의 상품 코드가 같은 조건이다. 

---

32.4 내부결합을 활용한 데이터 관리 p. 295

예제 7-11) 메이커 테이블 작성하기

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

메이커 테이블

---

예제 7-12) 상품 테이블과 메이커 테이블을 내부결합하기 

상품2 테이블

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

메이커 테이블에는 'M', 상품 테이블에는 'S'라는 짧은 별명을 붙였다.

별명.변수명
S.상품명 (from Table 상품2)
M.메이커명 (from Table 메이커)

---

1) 외부키
메이커 테이블의 메이커코드는 기본키이다. 
그에 비해 상품 테이블의 메이커코드는 '외부키'라 불린다. 다른 테이블의 기본키를 참조하는 열이 외부키가 된다. 

2) 자기 결합(Self Join) 

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

---

예제 7-13) 상품 테이블을 자기결합하기

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

자기 결합에서는 결합의 좌우가 같은 테이블이 되기 때문에 반드시 별명을 붙여야 한다.
자기 결합은 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우에 자주 사용된다.

select 상품명, 메이커명, S.메이커코드
from 상품2 S, 메이커 M
where S.메이커코드 = M.메이커코드
and S.메이커코드 = 'M001';

select 상품명, 메이커명, S.메이커코드
from 상품2 S inner join 메이커 M
on S.메이커코드 = M.메이커코드
where S.메이커코드 = 'M001';

두 코드의 결과는 같다.

---

32.5 외부결합

LEFT JOIN
LEFT JOIN은 첫 번째 테이블을 기준으로, 두 번째 테이블을 조합하는 JOIN입니다. 

이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블의 필드 값은 그대로 가져옵니다.
하지만 해당 레코드의 두 번째 테이블의 필드 값은 모두 NULL로 표시됩니다.

문법
첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건

ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있습니다.

다음 예제는 Reservation 테이블의 Name 필드를 기준으로 Customer 테이블의 Name 필드와 일치하는 레코드만을 LEFT JOIN으로 가져온 후, 그 중에서 ReserveDate 필드의 값이 2016년 02월 01일 이후인 레코드만을 선택하는 예제입니다.
예제) 

SELECT *
FROM Reservation
LEFT JOIN Customer
ON Reservation.Name = Customer.Name
WHERE ReserveDate > '2016-02-01';

결과) 

위의 예제에서 두 개의 Name 값이 일치하면, INNER JOIN과 같이 두 테이블의 모든 필드를 그대로 가져옵니다. 하지만 두 개의 Name 값이 일치하지 않는 경우에는 Customer 테이블의 모든 필드를 NULL로 표시됩니다.

LEFT JOIN의 결과를 벤 다이어그램으로 나타내면 다음과 같습니다.

---

RIGHT JOIN
RIGHT JOIN은 LEFT 조인과는 반대로 두 번째 테이블을 기준으로, 첫 번째 테이블을 조합하는 JOIN입니다. 
 
이때 ON 절의 조건을 만족하지 않는 경우에는 두 번째 테이블의 필드 값은 그대로 가져옵니다.
하지만 해당 레코드의 첫 번째 테이블의 필드 값은 모두 NULL로 표시됩니다.

문법
첫번째테이블이름
RIGHT JOIN 두번째테이블이름
ON 조건

ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있습니다.

다음 예제는 Customer 테이블의 Name 필드를 기준으로 Reservation 테이블의 Name 필드와 일치하는 레코드만을 RIGHT JOIN으로 가져오는 예제입니다.

예제)

SELECT *
FROM Reservation
RIGHT JOIN Customer
ON Reservation.Name = Customer.Name;

결과) 

위의 예제에서 두 개의 Name 값이 일치하면, INNER JOIN과 같이 두 테이블의 모든 필드를 그대로 가져옵니다. 하지만 두 개의 Name 값이 일치하지 않는 경우에는 Reservation 테이블의 모든 필드를 NULL로 표시됩니다.

RIGHT JOIN의 결과를 벤 다이어그램으로 나타내면 다음과 같습니다.

참고: http://www.tcpschool.com/mysql/mysql_multipleTable_join

---

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

예제 7-14) 내부 결합에서는 상품코드가 0009인 상품이 제외된다.

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

예제 7-15) 외부결합으로 상품코드 0009인 상품도 결과에 포함하기

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

Point: LEFT JOIN, RIGHT JOIN으로 외부결합을 할 수 있다. 

---

- 구식방법에서의 외부결합과 표준 SQL 
예제 7-16) Oracle에서 구식 외부 결합으로 0009의 상품을 결과에 포함하기

구식 결합방법에는 WHERE구로 결합 조건을 지정하고 외부결합으로 진행하고 싶은 경우 (+) 연산자를 사용한다. (Oracle)

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

 

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

Point: 구식 결합방법은 사용하지 않는다.

---

33강 관계형 모델

---

33.1 관계형 모델

Point: 릴레이션은 테이블을 말한다. 

---

33.2 관계형 모델과 SQL

1. 합집합 (Union)
2. 차집합 (Difference)
3. 교집합 (Intersection)
4. 곱집합 (Cartesian product)
5. 선택 (Selection)
6. 투영 (Projection)
7. 결합 (Join)

---