[필기정리] Day63 - 릴레이션 연산, JOIN 등

# UNION(합집합)

   각 SELECT 명령의 실행결과(집합)를 합집합(UNION)으로 계산하여 최종적으로 결과를 반환한다.

 ex)

CREATE TABLE sample71_a(a int);
INSERT INTO sample71_a VALUES(1);
INSERT INTO sample71_a VALUES(2);
INSERT INTO sample71_a VALUES(3);

CREATE TABLE sample71_b(b int);
INSERT INTO sample71_b VALUES(2);
INSERT INTO sample71_b VALUES(10);
INSERT INTO sample71_b VALUES(11);

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

UNION을 이용하면 여러 개의 SELECT 명령을 하나로 묶을 수 있다.

이 때 각각의 SELECT 명령의 열의 내용은 서로 일치해야 한다.

 

ex) 열 구성이 다른 테이블

SELECT * FROM sample71_a
UNION
SELECT * FROM sample71_b
UNION
SELECT * FROM sample31;  # 에러 발생

 

ex) 열 지정 활용 예시 

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

 

SELECT 명령들은 UNION으로 묶을 때 나열 순서는 합집합의 결과에 영향을 주지 않지만

ORDER BY 미 사용 시 결과값의 순서는 영향을 끼칠 수 있다.

ex) 결과값 나열 순서 예시

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

 

- UNION을 사용할 때의 ORDER BY

  UNION으로 SELECT 명령을 연결하는 경우,

  가장 마지막 SELECT 명령에 대해서만 ORDER BY 구를 지정할 수 있다!

  ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다!

ex) 에러 예시

SELECT a FROM sample71_a ORDER BY a
UNION
SELECT b FROM sample71_b;        # ERROR첫 번째 SELECT 명령에 ORDER BY를 지정할 수 없다.

SELECT a FROM sample71_a
UNION
SELECT b FROM sample71_b ORDER BY b; # ERROR

 

ex) 동일 별명 활용 예시

SELECT a AS c FROM sample71_a
UNION
SELECT b AS c FROM sample71_b ORDER BY c;

  

# UNION ALL

UNION의 경우는 기본 동작이 DISTINCT이고,

모든 결과를 얻고 싶을 때는 ALL을 추가적으로 지정한다.

 

ex) 

SELECT * FROM sample71_a
UNION ALL
SELECT * FROM sample71_b;

UNION에서는 이미 존재하는 값인지를 검사하는 처리가 필요한 만큼,

UNION ALL 쪽이 성능적으로는 유리할 경우가 있다.

즉, 중복값이 없는 경우에는 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.

 

// Tip : 데이터베이스에 외부파일 넣기

c:\sql 폴더에 sample.dump를 복사

sql 폴더로 이동

mysql -u root -p < sample.dump

1234

 

# 테이블 결합(JOIN)

  여러 개로 나뉜 데이터를 하나로 묶어 결과를 내는 방법

 

- 교차결합(Cross Join) (= 카티션 프로덕트((Cartesian product))

  두 개의 집합을 곱하는 연산 방법

 

ex) 드라마 시즌 예시

① 집합 X는 {1, 2, 3}라는 요소를 가진다.

② 집합 Y는 {1, 2, 3, 4, 5, 6}이라는 세 개의 요소를 가진다.

③ 집합 X의 요소 A에 집합 Y의 요소를 붙여 계산하는 것이다.

 

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

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

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

 

ex)

CREATE TABLE sample72_x(x char(4));
INSERT INTO sample72_x values('A');
INSERT INTO sample72_x values('B');
INSERT INTO sample72_x values('C');

CREATE TABLE sample72_y(y int);
INSERT INTO sample72_y values(1);
INSERT INTO sample72_y values(2);
INSERT INTO sample72_y values(3);

SELECT * FROM sample72_x;
# 실행 결과
x
A
B
C

SELECT * FROM sample72_y;
# 실행 결과
y
1
2
3

 

ex) FROM구에 테이블 두 개를 지정해 곱집합 구하기

SELECT * FROM sample72_x, sample72_y;
# 실행결과
x          y
A          1
B          1
C          1
A          2
B          2
C          2
A          3
B          3
C          3

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

 

//Tip : UNION 연결과 결합 연결의 차이

① UNION으로 합집합을 구했을 경우에는 세로 방향(행)으로 더해지게 된다.

② FROM 구로 테이블을 결합할 경우에는 가로 방향(열)으로 더해지게 된다.

 

- INNER JOIN(내부결합) : 두 개 테이블을 가로로 결합하는 것

① where절 사용

ㄴ ⓐ FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다.

    ⓑ 교차결합을 하면 곱집합으로 계산된다.

    ⓒ WHERE 조건을 지정해 곱집합에서 필요한 조합만 검색할 수 있다.

 

ex) 상품 테이블

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

ex) 재고 수 테이블

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

# 원하는 결과
상품명          재고수
상품명**        200
상품명@@        500

ex) 상품코드가 같은 행을 검색하기

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

 ex) 검색할 행과 반환할 열 제한하기

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

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

첫 번째 조건식은 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것이다.

두 번째 조건식은 결합 조건이 아닌 검색 조건이다.

여기에서 첫 번째 조건식의 조건을 '결합조건'이라 부른다.

 

② INNER JOIN

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

ex)

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

테이블과 테이블 사이에 'INNER JOIN'이라는 키워드를 넣는다.

ON을 사용하여 결합조건을 지정한다.

 

ex) 메이커 테이블 생성

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

DESC 메이커;
DESC 상품2;

SELECT * FROM 메이커;
SELECT * FROM 상품2;

 

- 테이블 별명 사용

ex)

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

 

// 외부키 : 다른 테이블의 기본키를 참조하는 열

 

- 자기결합(Self Join) 

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

ex) 

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

 

- OUTER JOIN(외부결합)

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

 

ex) 내부결합 후 문제점 발생 - 상품코드 0009번 누락

DESC 상품3;
DESC 재고수;
SELECT * FROM 상품3;
SELECT * FROM 재고수;

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

 

OUTERJOIN은 결합하는 테이블 중에 어느 쪽을 기준으로 할지 결정할 수 있다.

이번에는 상품 테이블(결합의 왼쪽)을 기준으로 INNER JOIN 대신 LEFT JOIN을 사용해 보겠다.

 

ex) 상품코드 0009인 상품 결과 포함

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

 

기준이 되는 상품 테이블을 JOIN의 왼쪽에 기술했으므로 LEFT JOIN이라 지정했다.

상품 테이블을 오른쪽에 지정하는 경우나 재고 테이블을 기준으로 삼고 싶은 경우에는

RIGHT JOIN을 사용해 외부결합을 시행한다.

 

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

 

- Oracle 외부결합 방식 (현재 사용 안함)

ex) where절 사용으로 0009의 상품을 결과에 포함하기

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

 

# 릴레이션 연산

- 합집합(union) : 릴레이션 간의 덧셈 (SQL - UNION)

ex)

CREATE TABLE A(no int); 
INSERT INTO A values(1); 
INSERT INTO A values(2); 
INSERT INTO A values(3); 

CREATE TABLE B(no int); 
INSERT INTO B values(2); 
INSERT INTO B values(10); 
INSERT INTO B values(11); 

SELECT * FROM A UNION SELECT * FROM B; // 만들어서 넣어야한다고 함

- 차집합(difference) : 릴레이션 간의 뺄셈 (SQL - EXCEPT) // MySQL에 없음

ex) SQL

SELECT * FROM A EXCEPT SELECT * FROM B;

 

ex) MySQL

SELECT A.no FROM A LEFT JOIN B on A.no = B.no WHERE B.NO IS NULL;

- 교집합(intersection) : 릴레이션 간의 교집합 (SQL - INTERSECT) // MySQL에 없음

ex) SQL

SELECT * FROM A INTERSECT SELECT * FROM B;

ex) MySQL

SELECT A.no FROM A JOIN B on A.no = B.no;

// Tip : EXCEPT와 INTERSECT는 PostgreSQL에서만 활용가능하다.

- 곱집합(cartesian product) : 릴레이션 간의 곱집합 (SQL - CROSS JOIN)

ex)

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

- 선택(selection) : 튜플의 추출 (SQL - WHERE절)

ex)

CREATE TABLE C(no int, a char(4)); 
INSERT INTO C VALUES(1, 'A'); 
INSERT INTO C VALUES(2, 'B'); 
INSERT INTO C VALUES(3, 'C'); 

SELECT * FROM C WHERE no < 3;

- 투영(projection) : 속성의 추출 (SQL - SELECT절)
ex)

SELECT a FROM C;

- 결합(JOIN) : 릴레이션 간의 교차결합 (SQL - INNER JOIN)

ex)

- 관계형 모델
관계형 모델은 관계형 데이터베이스의 기반이 되는 이론적 개념으로, 

사용되는 용어는 SQL과 일치하지 않는다.

# 연습문제
Q1. 테이블을 결합할 때 결합조건을 지정하지 않고 실행하면 구할 수 있는 집합은 무엇인가?
① 합집합
② 교집합
③ 곱집합
A. ③ 곱집합 : 결합조건을 지정하지 않을 경우에는 교차결합이 된다. 

                  교차결합은 곱집합으로 계산된다.

Q2. 내부결합 할 때 사용하는 키워드는 무엇인가?
① INNER JOIN
② LEFT JOIN
③ RIGHT JOIN
A. ① INNER JOIN

Q3. 관계형 모델에서 릴레이션에 해당하는 SQL 용어는 무엇인가?
① 테이블
② 열
③ 행
A. ① 테이블

 

ex)

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

CREATE TABLE 상품22( 
상품코드 CHAR(4) NOT NULL, 
상품명 varchar(30), 
메이커코드 CHAR(4), 
FOREIGN KEY(메이커코드) 
REFERENCES 메이커2(메이커코드) 
);

INSERT INTO 메이커2 VALUES('M001', '나이키'); 
INSERT INTO 메이커2 VALUES('M002', '리복'); 

INSERT INTO 상품22 VALUES('P001', '축구화', 'M001'); 
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002'); 
INSERT INTO 상품22 VALUES('P003', '농구화', 'M003'); #Error