이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.
join이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.
두 테이블을 엮어야만 원하는 형태가 나오는 경우도 많다.
인터넷 마켓 데이터베이스의 회원 테이블과 구매 테이블을 예로 들 수 있다.
회원 테이블에는 회원의 이름과 연락처가 있고, 구매 테이블에는 회원이 구매한 물건이 있다.
물건을 배송하려면 회원 테이블의 회원 이름과 연락처, 구매 테이블의 회원이 구매한 물건에 대한 정보가 함께 필요하다.
이렇게 두 테이블을 엮어서 하나의 배송을 위한 정보를 추출하는 것이 대표적인 join이다.
내부 조인(Inner Join)
두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인이다.
그냥 조인이라 부르면 내부 조인을 의미하는 것이다.
두 테이블의 조인을 위해서는 기본키(PRIMARY KEY, PK)와 외래키(FOREIGN KEY, FK) 관계로 맺어져야 하고, 이를 일대다 관계라고 한다.
일대다 관계의 이해
두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 한다.
데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.
이 분리된 테이블은 서로 관계를 맺고 있다.
이러한 대표적인 사례가 인터넷 마켓 데이터베이스(market_db)의 회원 테이블과 구매 테이블이다.
아래와 같이 morket_db에서 회원 테이블의 아이디와 구매 테이블의 아이디는 일대다 관계이다.
일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계를 말한다.
예를 들어, 회원 테이블에서 블랙핑크의 아이디는 BLK로 1명(1, one) 밖에 없다. 그래서 회원 테이블의 아이디를 기본 키로 지정했다.
구매 테이블의 아이디에서 3개의 BLK를 찾을 수 있다. 즉, 회원은 1명이지만 이 회원은 구매를 여러 번(다, many)할 수 있는 것이다.
그래서 구매 테이블의 아이디는 기본 키가 아닌 외래 키로 설정했다.
일대다 관계는 주로 기본 키와 외래 키 관계로 맺어져 있다. 그래서 일대다 관계를 PK-FK 관계라 부르기도 한다.
내부 조인의 기본
내부 조인의 형식은 아래와 같다.
구매 테이블에는 물건을 구매한 회원의 아이디와 물건 등의 정보만 있다.
이 물건을 배송하기 위해서는 구매한 회원의 주소 및 연락처를 알아야 한다.
이 회원의 주소, 연락처를 알기 위해 정보가 있는 회원 테이블과 결합하는 것이 내부 조인이다.
SELECT * FROM buy INNER JOIN member ON buy.mem_id = member.mem_id;
이렇게 쿼리를 작성하면, 구매 테이블과 회원 테이블이 mem_id를 기준으로 결합이 된다.
즉, 구매 테이블의 모든 행이 회원 테이블과 결합이 된다.
구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 아래와 같이 조인해서 이름/주소/연락처 등을 검색할 수 있다.
SELECT *
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';
내부 조인의 간결한 표현
이것은 열이 너무 많아 복잡해 보이므로 필요한 아이디/이름/구매 물품/주소/연락처만 추출하려면 아래와 같이 퀴리를 작성한다.
SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;
별칭
FROM 절에 나오는 테이블의 이름 뒤에 별칭을 줄 수 있다.
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr,
CONCAT(M.phone1, M.phone2) AS '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
결과는 똑같이 나오는 것을 확인할 수 있다.
내부 조인의 활용
구매한 회원의 아이디/이름/구매한 제품/주소를 정렬하여 출력하면 아래와 같다.
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id
ORDER BY M.mem_id;
구매한 회원의 구매 기록과 더불어 구매하지 않은 회원의 이름/주소가 같이 검색되도록 하려면 아래와 같이 검색되도록 하려면 외부 조인을 사용해야 한다.
즉, 지금까지 사용한 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이다.
만약, 양쪽 중에 한 곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다.
구매 이력이 있는 회원들에게 감사문 보내기 구매를 한 이력이 있는 회원들에게 감사문을 보내려면, 구매를 몇번 했는지와 상관없이 한 번만 구매를 했으면 감사문을 보낼 대상이 된다. 즉, 중복을 제거해야 한다.
SELECT DISTINCT M.mem_id, M.mem_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id
ORDER BY M.mem_id;
외부 조인(Outer Join)
내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나온다.
이와 달리 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.
LEFT OUTER JOIN: 왼쪽 테이블의 모든 값이 출력되는 조인
RIGHT OUTER JOIN: 오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN: 왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인
외부 조인의 기본
외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.
외부 조인의 형식은 아래와 같다.
내부 조인에서 해결하지 못한 전체 회원의 구매 기록(구매 기록이 없는 회원의 정보도 함께)출력을 외부조인으로 쿼리를 작성하면 아래와 같다.
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
ORDER BY M.mem_id;
member 테이블이 왼쪽 테이블, buy 테이블이 오른쪽 테이블인 상태이다.
RIGHT OUTTER JOIN으로 동일한 결과를 출력하려면 아래와 같이 단순히 왼쪽과 오른쪽 테이블의 위치만 바꾸면 된다.
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
RIGHT OUTER JOIN member M
ON M.mem_id = B.mem_id
ORDER BY M.mem_id;
buy 테이블이 왼쪽 테이블, member 테이블이 오른쪽 테이블인 상태이다.
FULL OUTTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이다.
왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력한다.
자주 사용하지 않는다.
외부 조인의 활용
회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록을 추출하는 쿼리는 아래와 같다.
SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
WHERE B.prod_name IS NULL
ORDER BY M.mem_id;
IS NULL 구문은 널(NULL) 값인지 비교한다.
외부 조인 정리
기타 조인
내부 조인이나 외부 조인처럼 자주 사용되지는 않지만 가끔 유용하게 사용되는 조인으로 상호 조인과 자체 조인도 있다.
상호 조인(Cross Join)
한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다. 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수만큼 됩니다. 카티션 곱(CARTESIAN PRODUCT)라고도 한다.
회원 테이블의 첫 행은 구매 테이블의 모든 행과 조인된다.
나머지 행도 마찬가지이다.
즉, 회원 테이블의 첫 행이 구매 테이블의 12개 행과 결합된다.
또 회원 테이블의 두 번째 행이 구매 테이블의 12개 행과 결합된다.
이런 식으로 회원 테이블의 모든 행이 구매 테이블의 모든 행과 결합된다.
최종적으로 회원 테이블의 10개 행과 구매 테이블의 12개 행을 곱해서 총 120개의 결과가 생성된다.
상호 조인은 아래와 같은 특징을 갖는다.
ON 구문을 사용할 수 없다.
결과의 내용은 의미가 없다.(랜덤으로 조인하기 때문)
상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때 쓰인다.
자체 조인(Self Join)
내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인했다.
자체 조인은 자신이 자신과 조인한다는 의미이다.
그래서 자체 조인은 1개의 테이블을 사용한다.
예를 들어, 관리 이사는 직원이므로 직원 열에 속한다.
그러면서 동시에 경리부장과 인사부장의 상관이어서 직속 상관 열에도 속한다.
만약, 직원 중 경리부장의 직속상관인 관리이사의 사내 연락처를 알고 싶다면 EMP열과 MANAGER 열을 조인해야 한다.
CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));
INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');
이렇게 만든 테이블은 아래와 같은 구조를 갖는다.
이제 관리 이사의 사내 전화번호를 알고 싶다면 아래와 같은 쿼리를 작성하면 된다.
SELECT A.emp "직원" , B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A
INNER JOIN emp_table B
ON A.manager = B.emp
WHERE A.emp = '경리부장';
즉, 아래와 같이 2개의 테이블이 조인 되는 것처럼 구성된 것이다.
이렇든 하나의 테이블에 같은 데이터가 있지만 2개 이상의 열로 존재할 때 자체 조인을 할 수 있다.
