Day05-1. Join - INNER JOIN

Join

• 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것.
• 일대다 관계란, 김범수 사용자의 예를 들면, 김범수는 회원가입 시에 KBS로 아이디를 지정했는데, 아이디 열이 Primary Key로 지정되어 있으므로, 타 사용자는 KBS 아이디를 쓸 수가 없게 된다. 이것이 일대다 관계에서 1에 해당.
• 구매 테이블(buytbl)을 살펴보면, 만약 구매 테이블의 아이디 열을 회원 테이블과 동일하게 Primary Key로 지정을 해버린다면?... Primary Key는 한 번만 들어갈 수 있으므로 사용자는 물건을 한 번 구매한 이휴에는 또 다시 물건을 살 수 없게 설정이 될 것이다. 그래서 회원 테이블의 아이디는 Primary Key로 설정된 것이며, 구매 테이블의 아이디는 Primary Key와 관련되는 Foreign Key로 설정한 것이다.

 

Inner Join (내부 조인)

• 현재 구매 테이블에서는 물건을 구매한 사용자의 아이디와 물건 등의 정보만 나타난다. 
• 이 물건을 배송하기 위해서는 회원의 주소를 알아야 하는데, 이건 회원 테이블에 있다. 그래서 두 테이블을 연결해 나타내야 한다.

형식

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색조건]

 

방법

• userTBL 과 buyTBL을 검색하여 공통 요소인 userID를 추출한다. 그래서 그걸 가지고 두 행을 Join 하면 됨
• 즉, 여기서 JYP 아이디를 가진 사람이 구매한 물건을 발송하기 위해서 이름/주소/연락처 등을 조인해서 검색해보면 다음과 같다.
• 여기서는 구매 테이블의 userID인 JYP를 추출한다.
• 그리고 JYP와 동일한 값을 회원 테이블의 userID 열에서 검색한 후 'JYP'라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합한다.

USE sqldb;
SELECT * FROM buytbl b
	INNER JOIN usertbl u ON b.userID = u.userID
WHERE buytbl.userID = 'JYP';

마지막 문장을 빼면 buytbl의 모든 행에 대해서 위와 동일한 방식으로 반복하게 된다.

 

실행 결과

 

필요한 열(ID/이름/구매물품/주소/연락처)만 추출해보기

SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS 연락처 FROM buytbl
	INNER JOIN usertbl ON buytbl.userId = usertbl.userID
ORDER BY num;

--> 이건 에러 나옴. 열 이름 userID가 불확실하기 때문, 이 경우에는 어느 테이블의 userID를 추출할 지 선택해야 함.

    동일한 값이지만 지금은 buytbl을 기준으로 하는 것이므로 buytbl의 userID가 더 정확.

 

수정

SELECT buytbl.userID, name, prodName, addr, 
		CONCAT(mobile1, mobile2) AS 연락처 FROM buytbl
	INNER JOIN usertbl ON buytbl.userId = usertbl.userID
ORDER BY num;

실행 결과

이걸 where절로 변경

-- 위와 동일함
SELECT buytbl.userID, name, prodName, addr, mobile1 + mobile2 AS '연락처' FROM buytbl, usertbl
WHERE buytbl.userID = usertbl.userID
ORDER BY num;

- where절 쓰는 거는 확실히 INNER JOIN이 맞다.

 

 

수정2 - SELECT 다음의 컬럼 이름(열 이름)에도 모두 '테이블이름.열 이름' 식으로 붙여주기

SELECT buytbl.userID, usertbl.name, buytbl.prodName, usertbl.addr, 
		CONCAT(usertbl.mobile1, usertbl.mobile2) AS 연락처 FROM buytbl
	INNER JOIN usertbl ON buytbl.userId = usertbl.userID
ORDER BY buytbl.num;

 

수정3- 별칭 붙여주기

SELECT b.userID, u.name, b.prodName, u.addr, CONCAT(u.mobile1, u.mobile2) AS 연락처 
	FROM buytbl b
	INNER JOIN usertbl u ON b.userId = u.userID
ORDER BY b.num;

 

이번에는 같은 결과이지만 다음과 같이 아이디/이름/물품/주소/연락처만 출력되도록 하고, 코드도 간결하게 수정.

SELECT b.userID, u.name, b.prodName, u.addr, CONCAT(u.mobile1, u.mobile2) AS 연락처 
	FROM buytbl b
	INNER JOIN usertbl u ON b.userId = u.userID
WHERE b.userID = 'JYP';

=> JYP아이디를 쓰는 회원이 구매한 정보만 출력.

 

실행 결과

 

이제 회원 테이블을 기준으로 JYP 아이디가 구매한 물건의 목록을 보면 

- 구매 테이블을 기준으로 한 것에서 순서 정도 바뀜.

 

이번에는 전체 회원들이 구매한 목록을 모두 출력하기. 즉, WHERE 조건만 빼면 된다.

+ 회원 아이디 순으로 정렬

SELECT u.userID, b.prodName, u.addr, CONCAT(u.mobile1, u.mobile2) AS '연락처' FROM usertbl U
	INNER JOIN buytbl b ON u.userID = b.userID
ORDER BY u.userID;

실행 결과

 

그런데 이 결과는 사전적 의미의 전체 회원들은 아니다.

정확히 말하면 전체 회원들이 아닌 '구매한 기록이 있는 회원들의 결과' 이다.

여기에는 한 번도 구매한 적이 없는 이승기, 김경호, 임재범, 윤종신, 조관우는 나오지 않았다.

 

만약 구매한 회원의 기록도 나오면서 더불어 구매하지 않았어도 회원의 이름/주소 등은 나오도록 조인할 필요가 있을 수도 있는데,  이 때 사용하는 것이 OUTER JOIN(뒤에 나옴)

 

INNER JOIN 은 양쪽 테이블에 모두 내용이 있는 것만 조회됨.

OUTER JOIN은 양쪽 테이블에 모두 내용이 있는 것 + 한쪽에만 내용이 있어도 결과가 조회되는 조인 방식

 

쇼핑몰에서 한 번이라도 구매 기록이 있는 우수회원들에게 안내문을 발송하도록, 조건에 맞는 회원들 출력

(중복제거)

SELECT DISTINCT u.userID, U.name, u.addr FROM usertbl u -- Distinct!!!
	INNER JOIN buytbl b ON u.userID = b.userID
ORDER BY u.userID ;

실행 결과

 

EXISTS 문으로도 동일한 결과 낼 수가 있다.

SELECT u.userID, u.name, u.addr FROM usertbl u
WHERE EXISTS (
	SELECT * FROM buytbl b
    WHERE u.userID = b.userID );

 

---

세 개 테이블 조인하기

학생과 동아리의 관계

• 한 학생은 여러 개 동아리에 가입하여 활동할 수 있고,
• 하나의 동아리에는 여러 명의 학생이 가입하여 활동할 수 있으므로 둘은 다대다 관계
• 논리적으로는 구성이 가능하나, 물리적으로 구성 위해서는 두 테이블 사이에 연결 테이블을 둬서 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성해야 한다.

이 예시에서는 학생의 이름 및 동아리명을 PRIMARY KEY로 설정함

(근데, 실제로 학생이름을 PRIMARY KEY로 설정하는 것은 멍청한 행위, 동명이인은 다 자퇴해야하는 헤프닝)

• 현재 이 구조는 학생 테이블과 동아리 테이블은 서로 직접적인 관련이 없다. 하지만, 중간의 학생_동아리 테이블이 두 테이블의 연관관계를 맺어주고 있다.

테이블 생성 후 데이터 입력

USE sqldb;
CREATE TABLE stdTBL (
  stdName VARCHAR(10) NOT NULL PRIMARY KEY,
  addr CHAR(4) NOT NULL
);

CREATE TABLE clubTBL (
  clubName VARCHAR(10) NOT NULL PRIMARY KEY,
  roomNo VARCHAR(4) NOT NULL
);

CREATE TABLE stdclubTBL (
  num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  stdName VARCHAR(10) NOT NULL,
  clubName VARCHAR(10) NOT NULL,
  FOREIGN KEY(stdName) REFERENCES stdTBL(stdName),
  FOREIGN KEY(clubName) REFERENCES clubTBL(clubName)
);


INSERT INTO stdtbl VALUES
('김범수','경남'), ('성시경','서울'), ('조용필','경기'), ('은지원','경북'), ('바비킴','서울');

INSERT INTO clubtbl VALUES
('수영','101호'), ('바둑','102호'), ('축구','103호'), ('봉사','104호');

INSERT INTO stdclubtbl VALUES
(NULL,'김범수','바둑'), (NULL,'김범수','축구'), (NULL,'조용필','축구'), (NULL,'은지원','축구'),
(NULL,'은지원','봉사'), (NULL,'바비킴','봉사');

 

 

학생 테이블, 동아리 테이블, 학생동아리 테이블을 사용해서

학생을 기준으로 학생이름 / 지역 / 가입한 동아리 / 동아리방을 출력

 

사전작업 1

SELECT s.stdName, addr, clubName FROM stdtbl s -- 공통 stdName
INNER JOIN stdclubtbl sc ON sc.stdName = s.stdName
ORDER by s.stdName;

결과

 

사전작업2

SELECT c.clubName, c.roomNo, sc.stdName FROM clubtbl c
INNER JOIN stdclubtbl sc ON sc.clubName = c.clubName
ORDER BY c.clubName;

결과

 

이제 이거 두 개만 잘 이어주면 됩니다.

SELECT s.stdName, a.addr, c.clubName, c.roomNo FROM stdtbl s
INNER JOIN stdclubtbl sc ON s.stdName = sc.stdName
INNER JOIN clubtbl c ON sc.clubName = c.clubName
ORDER BY s.stdName;

=> 학생동아리 테이블과 학생 테이블의 일대다 관계를 INNER JOIN 하고, 또한 학생동아리 테이블과 동아리 테이블의 일대다 관계를 INNER JOIN 한다.

정답

 

---

문제2

동아리(club)를 기준으로 가입한 학생의 목록 출력하기

- 출력 바꾸고, 정렬되는 기준을 동아리 이름으로 변경

SELECT c.clubName, c.roomNo, s.stdName, s.addr FROM stdtbl s
INNER JOIN stdclubtbl sc ON sc.stdName = s.stdName
INNER JOIN clubtbl c ON sc.clubName = c.clubName
ORDER BY c.clubName;

정답

---

문제 3. 동아리가 축구인 학생들만 전체 나오게 출력.

--> where절 활용

SELECT s.stdName, sc.clubName, c.roomNo FROM stdtbl s
INNER JOIN stdclubtbl sc ON sc.stdName = s.stdName
INNER JOIN clubtbl c ON c.clubName = sc.clubName
WHERE sc.clubName = "축구";

실행 결과