[MySQL] JOIN ①

📌 JOIN

• 두 테이블에 대한 공통키(컬럼)을 기준으로 하나의 행(튜플)으로 합침
• 조인 종류
  - 내부조인(INNER JOIN)
  - 외부조인(OUTER JOIN)

---

데이터베이스 생성

-- 테이블의 데이터형식 및 제약 조건 변경
DROP SCHEMA IF EXISTS bookdb;
CREATE SCHEMA bookdb;
USE bookdb;

# 테이블 publisher
-- 데이터 유형 변경
ALTER TABLE publisher
	MODIFY pubNo VARCHAR(10) NOT NULL,
    MODIFY pubName VARCHAR(20);
    
-- 제약조건 추가
ALTER TABLE publisher
	ADD CONSTRAINT PK_publisher_pubNo
    PRIMARY KEY (pubNo);

# 테이블 book
-- 데이터 유형 변경 
ALTER TABLE book
	MODIFY bookNo VARCHAR(10) NOT NULL,
    MODIFY bookName VARCHAR(20),
    MODIFY bookAuthor VARCHAR(30),
    MODIFY bookDate DATE,
    MODIFY pubNo VARCHAR(10) NOT NULL;

-- 제약조건 추가
ALTER TABLE book
	ADD CONSTRAINT PK_book_bookNo
    PRIMARY KEY (bookNo);
    
ALTER TABLE book
    ADD CONSTRAINT FK_book_pubNo
    FOREIGN KEY (pubNo)
    REFERENCES publisher(pubNo);

# 테이블 bookclient
-- 데이터 유형 변경
ALTER TABLE bookclient
	MODIFY clientNo VARCHAR(10) NOT NULL,
    MODIFY clientName VARCHAR(30),
    MODIFY clientPhone VARCHAR(15),
    MODIFY clientAddress VARCHAR(50),
    MODIFY clientBirth DATE,
    MODIFY clientHobby VARCHAR(30),
    MODIFY clientGender VARCHAR(1);
    
-- 제약조건 추가
ALTER TABLE bookclient
	ADD CONSTRAINT PK_bookclient_clientNo
    PRIMARY KEY (clientNo);

# 테이블 booksale
-- 데이터 유형 변경
ALTER TABLE booksale
	MODIFY bsNo VARCHAR(10) NOT NULL,
    MODIFY bsDate DATE,
    MODIFY clientNo VARCHAR(10) NOT NULL,
    MODIFY bookNo VARCHAR(10) NOT NULL;
    
-- 제약조건 추가
ALTER TABLE booksale
	ADD CONSTRAINT PK_booksale_bsNo
    PRIMARY KEY (bsNo);
    
ALTER TABLE booksale
    ADD CONSTRAINT FK_booksale_clientNo
    FOREIGN KEY (clientNo)
    REFERENCES bookclient(clientNo);

ALTER TABLE booksale
    ADD CONSTRAINT FK_booksale_bookNo
    FOREIGN KEY (bookNo)
    REFERENCES book(bookNo);

---

테이블 데이터

book	bookclient

booksale	publisher

---

1️⃣ INNER JOIN (내부 조인)

• 내부조인(INNER JOIN):
  - 공통 컬럼의 속성 값이 동일한 튜플(레코드)만 반환
  

1) WHERE 절 사용

SELECT 열리스트
FROM 테이블1, 테이블2
WHERE 테이블1.키 = 테이블2.키;

-- 도서 주문한 고객 명단(이름)과 주문량 출력
SELECT bookdb.bookclient.clientNo,
	   bookdb.bookclient.clientName, 	
       bookdb.booksale.bsQty
	FROM bookdb.booksale, bookdb.bookclient
	WHERE bookdb.booksale.clientNo = bookdb.bookclient.clientNo;

- 출력

★ FROM 문에서 SCHEMA명.TABLE명 을 별칭으로 지정하여 사용

SELECT BC.clientNo,
	   BC.clientName, 	
       BS.bsQty
	FROM bookdb.booksale BS, bookdb.bookclient BC
	WHERE BS.clientNo = BC.clientNo;

---

2) JOIN ~ ON 사용

SELECT 열리스트
FROM 테이블A
JOIN 테이블B
ON 테이블A.컬럼c = 테이블B.컬럼c;

SELECT BC.clientNo,
	   BC.clientName, 	
       BS.bsQty
	FROM bookdb.bookclient BC
	JOIN bookdb.booksale BS
    ON BC.clientNo = BS.clientNo;

---

3) INNER JOIN ~ ON 사용

SELECT 열리스트
FROM 테이블A
INNER JOIN 테이블B
ON 테이블A.컬럼c = 테이블B.컬럼c;

SELECT BC.clientNo,
	   BC.clientName, 	
       BS.bsQty
FROM bookdb.bookclient BC
INNER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo;

• 결과는 1) ~ 3) 모두 같다.

---

• 다음과 같이 쿼리문을 작성하면 공통열이 두 번 출력되게 되어 필요한 컬럼을 지정하여 사용한다. (clientNo 이 두 번)

SELECT *							
FROM bookdb.bookclient BC
INNER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo;

- 출력

---

• 필요한 컬럼만 지정하며 동시에 중복 행을 삭제 (DISTINCT)
• 결과 정렬도 가능하다. (ORDER BY)

SELECT DISTINCT BC.clientNo, BC.clientName 	
FROM bookdb.bookclient BC
INNER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo
ORDER BY BC.clientName;

---

4) 세 테이블 조인

-- 주문한 고객의 정보(번호, 이름), 도서정보(책제목, 책가격), 주문정보(주문번호, 주문일자, 주문수, 주문금액)를 최근 주문일자 기준으로 정렬하여 출력
SELECT BS.bsNo, BS.bsDate, BC.clientNo, BC.clientName, B.bookName,
	   B.bookPrice, BS.bsQty, B.bookPrice * BS.bsQty AS 주문금액
FROM bookdb.booksale BS
INNER JOIN bookdb.bookclient BC ON BS.clientNo = BC.clientNo
INNER JOIN bookdb.book B ON BS.bookNo = B.bookNo
ORDER BY BS.bsDate DESC;

---

2️⃣ OUTER JOIN (외부 조인)

• 두 테이블의 공통컬럼이 있는 경우 공통컬럼의 속성 값이 없는 튜플도 반환

1) 좌측외부조인 (LEFT OUTER JOIN)

• 왼쪽 테이블의 모든 정보 유지

SELECT 열리스트
FROM 테이블1
LEFT OUTER JOIN 테이블2
ON 조인조건(기본키=외래키);

• 왼쪽 테이블인 bookclient 테이블의 정보는 모두 유지하면서 LEFT OUTER JOIN 을 진행한 오른쪽 테이블인 bookdsale 테이블의 정보가 없어도 NULL 값으로 표시한 모습이다.

SELECT *
FROM bookdb.bookclient BC
LEFT OUTER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo
ORDER BY BC.clientNo;

- 출력

Ex) 고객(client) 중 한번도 구매한 적이 없는 고객 명단을 구해보자.

• 고객명단(bookclient)은 유지하면서 판매기록(booksale)엔 없는 고객 정보를 구하면 되기에 bookclient를 왼쪽에 둔 LEFT OUTER JOIN을 진행하면 된다.

-- 고객 중 한번도 구매한 적이 없는 고객 출력
SELECT BC.clientNo, BC.clientName
FROM bookdb.bookclient BC
LEFT OUTER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo
WHERE BS.clientNo IS NULL
ORDER BY BC.clientNo;

---

2) 우측외부조인 (RIGHT OUTER JOIN)

• 오른쪽 테이블의 모든 정보 유지

SELECT 열리스트
FROM 테이블1
RIGHT OUTER JOIN 테이블2
ON 조인조건(기본키=외래키);

Ex) 한번이라도 주문한 적이 있는 고객의 번호와 이름을 출력하되 중복된 경우 한번만 출력해보자.

• 판매기록(booksale) 정보를 유지하며 해당 테이블에 있는 clientNo를 통해 고객정보를 출력해야하기에 booksale 테이블을 오른쪽에 둔 RIGHT OUTER JOIN를 진행한다.

-- 한번이라도 주문한 적이 있는 고객의 번호와 이름 출력, 중복된 경우 한번만 출력
SELECT DISTINCT BC.clientNo, BC.clientName
FROM bookdb.bookclient BC
RIGHT OUTER JOIN bookdb.booksale BS
ON BC.clientNo = BS.clientNo
ORDER BY BC.clientNo;

- 출력

---

3) 완전외부조인

• MySQL 의 경우 FULL OUTER JOIN 명령어가 없기 때문에 해당 조인을 실행하려면 LEFT OUTER JOIN 과 RIGHT OUTER JOIN 을 UNION 한다.

(SELECT *
	FROM bookdb.bookclient BC
    LEFT OUTER JOIN bookdb.booksale BS
    ON BC.clientNo = BS.clientNo)
UNION    
(SELECT *
	FROM bookdb.bookclient BC
    RIGHT OUTER JOIN bookdb.booksale BS
    ON BC.clientNo = BS.clientNo);