조인
지금까지 우리는 대개 하나의 테이블을 다루는 작업을 위주로 수행했다. 이를 기반으로 해서 지금부터는 두 개이상의 테이블이 서로 관계되어 있는 상태를 고려해 보자.
조인이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다.
*조인을 이해하기 위해서 꼭 필요한 개념 복습
데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장한다. 그리고 이 분리된 테이블들은 서로 관계(Relation)를 맺고 있다. 그 중에서 간단하지만 가장 많이 사용되는 보편적인 관계가 그림의 sqldb의 usertbl과 buytbl의 관계인 '1대다'의 관계다. 이 데이터베이스는 간단한 가상의 쇼핑몰에서 운영하는 데이터베이스로 가정한 것이며 그 중에서 회원의 기본 정보(usertbl)와 회원이 구매한 구매 정보(buytbl)만 표시한 것이다. 1대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 다른 쪽 테이블에는 여러 개가 존재할 수 있는 관계다.
먼저 회원 테이블(usertbl)을 살펴보자. 김범수 사용자는 회원 가입 시에 ID를 KBS로 생성했다. 그런데, 만약 이 'KBS'를 다른 사람도 사용할 수 있을까? 아이디 열은 Primary Key로 지정되어 있으므로 절대 동일한 아이디를 사용할 수가 없다. 그래서 KBS는 하나만 존재한다. 이것이 1대다 관계에서 '1'이다. PK-FK 관계.
INNER JOIN(내부 조인)
조인 중에서 가장 많이 사용되는 조인이다. 대개의 업무에서 조인은 INNER JOIN을 사용한다. 일반적으로 JOIN이라고 얘기하는 것이 이 INNER JOIN을 지칭하는 것이다.
INNER JOIN을 사용하기 위한 경우를 생각해 보자. 지금 구매 테이블을 보면, 물건을 구매한 사용자의 아이디와 물건 등의 정보만 나타난다. 그런데, 이 물건을 배송하기 위해서는 구매한 회원의 주소를 알아야 한다. 이 회원의 주소 정보를 알기 위해 주소 정보가 있는 회원 테이블과 결합하는 조인이 INNER JOIN이다. 우선, 형식을 살펴보자.
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
[WHERE 검색조건]위의 형식에서 INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식한다.
구매 테이블 중에서 JYP라는 아이디를 가진 살감이 구매한 물건을 발송하기 위해서 이름/주소/연락처 등을 조인해서 검색하려면 다음과 같이 작성하면 된다.
USE sqldb;
SELECT *
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
WHERE buytbl.userID = 'JYP';
우선, 구매 테이블의 userID(buytbl.userID)인 'JYP'를 추출한다. 그리고 'JYP'와 동일한 값을 회원 테이블의 userID(usertbl.userID)열에서 검색한 후 'JYP'라는 아이디를 찾으면 구매 테이블과 회원 테이블의 두 행을 결합(JOIN)한다.
USE sqlddb;
SELECT *
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
ORDER BY num;WHERE절이 없을때 쿼리 결과.
- MySQL 8.0.16 버전까지는 ORDER BY num 구문을 넣지 않아도 기준 테이블인 buytbl의 num열에 의해서 정렬되었으나 MySQL 8.0.17 버전에서는 ORDER BY num 구문을 넣지 않으면 userID열로 정렬된다. 정렬되는 것 외에 결과값이 달라지는 것이 없고, 별로 중요한 사항은 아니다. MySQL 버전에 따라서 차례가 조금 다르게 나오더라도 무시하고 넘어가자.
열의 항목이 너무 많은 것 같아서 복잡해 보이므로 이번에는 필요한 열만 추출해 보자. 아이디/이름/구매물품/주소/연락처만 추출하자.
SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
FROM buytbl
INNER JOIN usertbl
ON buytbl.userID = usertbl.userID
ORDER BY num;오류 메시지:
Error Code: 1052. Column 'userid' in field list is ambiguous열 이름 userID가 불확실하다는 오류 메시지가 나왔다. userID의 경우에는 두 테이블 모두에 들어 있어서 어느 테이블의 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;
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 num;각 열이 어느 테이블에 속한 것인지는 명확해졌지만, 코드가 너무 길어져 오히려 복잡해 보인다. 이를 간편하게 하기 위해서 다음과 같이 각 테이블에 별칭을 줄 수 있다. 다음 코드는 위와 동일하지만 훨씬 간결하다.
SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B
INNER JOIN usertbl U
ON buytbl.userID = usertbl.userID
ORDER BY num;테이블에 별칭을 주기 위해서는 간단히 FROM절에 나오는 테이블의 이름 뒤에 별칭을 붙여주면 된다. 앞으로는 여러 개의 테이블이 관련되는 조인에서는 이러한 방식을 사용할 것을 적극 권장한다.
이번에는 전체 회원들이 구매한 목록을 모두 출력해 보자. 지금 필자가 '전체 회원들'이라고 얘기한 것에 주목하자. 위의 쿼리문에서 WHERE 조건만 빼면 된다. 그리고 결과를 보기 쉽게 회원ID순으로 정렬하도록 하자.
SELECT U.userID, U.name, 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;
INNER JOIN이 한쪽에는 없는 목록만 나오기 때문에 유용한 경우도 있다. 예를 들어, "쇼핑몰에서 한번이라도 구매한 기록이 있는 우수회원들에게 감사의 안내문을 발송하도록 하자"의 경우에는 다음과 같이 DISTINCT문을 활용해서 회원의 주소록을 뽑을 수 있다.
SELECT DISTINCT U.userID, U.name, U.addr
FROM usertbl U
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 CAHR(4) NOT NULL
);
CREATE TABLE clubtbl
( clubName VARCHAR(10) NOT NULL PRIMARY KEY,
roomNo CHAR(4) NOT NULL
);
CREATE TABLE stdclubtbl
( num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
stdName VARCHAR(10) NOT NULL,
clubName VARCHAR(10) NO/t 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, '바비킴', '봉사')
SELECT S.stdName, S.addr, SC.clubName, C.roomNo
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDERY BY S.stdName;
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;
