SQL 고급 문법 (2)

prefer·2024년 12월 27일
Databasesql

SQL 기초

목록 보기
6/15

JOIN

JOIN이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다. 데이터베이스를 설계하다 보면, 테이블 끼리의 연관관계가 자연스럽게 생기게 된다. 이에 따라 데이터를 조회할 때 테이블 하나만을 조회하는 것이 아닌 다른 테이블, 또는 다른 테이블의 특정 열을 추출해야 하는 일이 많은데 이러한 경우에 JOIN이 사용된다.

SQL을 다루면서 상당히 자주 사용되고, 또 중요한 개념이기 때문에 반드시 정확하게 학습해놔야 한다. 조인은 대표적으로 INNER JOIN과 OUTER JOIN이 존재한다. 이외에도 더 많은 JOIN의 형식이 있으니 공부해보자.

일대다 관계(one to many, 1:N)

두 테이블의 조인하기 위해서는 두 테이블이 기본키-외래키 관계로 연결되어 있어야 한다. 여러 정보를 주제에 따라 분리된 테이블은 서로 관계(relation)를 맺는데 일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재 가능한 관계를 말한다.

예를 들어 회원 테이블에서 블랙핑크의 아이디는 ‘BLK’로 고유하기에 기본 키(Primary Key, PK)로 지정했다. 이에 따라 member 테이블에서 아이디가 BLK인 행은 하나로 유일하다. 그러나 구매 테이블의 아이디는 기본 키가 아니므로 BLK라는 아이디를 가진 행이 3개나 존재한다.

즉 회원은 1명(one)이지만 이 회원은 구매를(to) 여러 번(many) 할 수 있으므로 일대다 관계가 성립된다. 고로 구매 테이블의 아이디는 기본 키가 아닌 외래 키(Foreign Key, FK)로 설정한다.

일대다 관계는 주로 기본 키와 외래 키 관계로 맺어져 있으며 따라서 일대다 관계를 ‘PK-FK 관계’라고 부르기도 한다. 두 테이블의 조인을 위해서는 기본 키-외래 키 관계로 맺어져야하고 이것이 일대다 관계다.

그러나 꼭 기본 키-외래 키 관계가 아니어도 가능한 조인이 존재한다.(상호 조인, CROSS JOIN) 또한 조인은 3개 이상의 테이블도 가능하나 대부분 2개로 조인한다. 지나치게 많은 테이블을 조인하면 성능 상에 문제가 발생할 수 있으므로 아무리 많아도 3개 이하로 조인하도록 하자.

내부 조인(INNER JOIN)

내부 조인(INNER JOIN)은 두 테이블을 연결할 때 가장 많이 사용되며, 그냥 조인이라 부르면 내부 조인을 의미한다. 내부 조인이란 두 테이블 간에 공통된 값이 있는 행만 결합하는 유형의 조인이다.

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

따라서 INNER JOIN은 JOIN이라고 써도 INNER JOIN으로 인식된다. ON (조인 조건)에는 두 테이블을 묶는 조건을 명시한다. 아래의 예시로 확인해보자.

<실행>

USE market_db;
SELECT *
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';

<결과>

위 예시는 mem_id가 ‘GRL’인 구매자의 정보와 구매 정보를 가져오는 예시이다.

구매 테이블에서 구매한 회원의 주소 및 연락처 정보가 필요하므로 구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 조인을 사용하여 이름/주소/연락처 추출할 수 있다.

두 개의 테이블을 조인할 때 동일한 열 이름이 존재한다면 반드시 (테이블 이름).(열 이름) 형식으로 표기하여 테이블 간의 중복 제거가 필요하다. 위 예시가 그러한데 ON mem_id = mem_id로 작성할 경우 각각의 mem_id가 buy 테이블의 mem_id인지 member 테이블의 mem_id인지 모르기 때문이다.

두 테이블의 INNER JOIN은 다음과 같은 과정을 거친다.

  1. buy 테이블과 member 테이블의 데이터들을 가져온 뒤 buy 테이블의 mem_id와 member 테이블의 mem_id가 같은 모든 행을 결합한다.
  2. 결합된 행들 중에서, buy 테이블의 mem_id가 GRL인 행들을 선택한다.

순서상 WHERE보다 ON의 조건이 먼저 처리되므로, 우선 각각의 mem_id가 같은것끼리 결합 후, WHERE절의 조건이 처리됨을 기억하자.

<실행>

SELECT * FROM buy INNER JOIN member ON buy.mem_id = member.mem_id;

<결과>

아까 실행한 쿼리문에서 WHERE 절을 생략한 것으로, 각각의 mem_id가 같아서 결합된 모든 행이 출력됐다.

JOIN을 하여 두 테이블이 결합된 결과에서 *을 사용하여 출력하면 각각의 테이블이 가진 열을 모두 출력하는데, 원하는 열을 출력하려면 열 이름을 직접 지정해야 한다.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;

만약 특정 열 이름이 두 테이블에 모두 존재한다면 (테이블 이름).(열 이름)과 같이 테이블 이름 명시해야 한다. 이 과정에서 본래의 테이블 이름을 모두 지정하면 코드가 너무 길어져서 복잡해진다.

따라서 간결하게 표현하기 위해 FROM절에 나오는 테이블의 이름 뒤에 별칭(alias) 부여하여서 SELECT절과 ON절에 사용되는 테이블의 이름을 간략하게 사용할 수 있다.

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;

만약 내부 조인을 사용할 경우 예시에서 모든 회원을 조회하는 것이 아닌 구매 기록이 있는 회원들을 조회, 즉 한 번도 구매하지 않은 회원의 정보는 추출할 수 없다.

내부 조인은 두 테이블에 모두 있는 내용만 조인하기 때문에, 양쪽 중에 한곳이라도 내용이 있을 때 조인하려면 외부 조인(OUTER JOIN)을 사용해야 한다.

<실행>

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;

<결과>

한 번이라도 구매한 기록이 있는 회원들을 조회하려면 중복 제거가 필요하고, DISTINCT를 사용하여 중복을 제거할 수 있다.

외부 조인(OUTER JOIN)

외부 조인은 내부 조인과 다르게 두 테이블을 조회할 때 한쪽에만 데이터가 있어도 결과를 추출할 수 있다. 외부 조인이란 두 테이블 간의 데이터를 결합할 때, 조인 조건에 일치하지 않는 데이터도 포함할 수 있도록 허용하는 조인이다. ‘조인 조건에 일치하지 않아도 포함된다’에 해당되는 건 조인의 방향이 가르키지 않는 테이블이다. 아래에서 그 의미를 정확하게 알아보자.

SELECT 열 목록
FROM 첫 번째 테이블(LEFT 테이블)
<LEFT | RIGHT | FULL> OUTER JOIN 두 번째 테이블(RIGHT 테이블)
ON 조인 조건
[WHERE 검색 조건];

외부 조인은 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN가 존재한다. 참고로 앞에 LEFT, RIGHT, FULL이 붙으면 OUTER를 생략할 수 있고, 기본값이 없으므로 반드시 LEFT, RIGHT, FULL 중 하나를 명시해야 한다.

LEFT OUTER JOIN

LEFT OUTER JOIN, LEFT 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 순서로 테이블을 언급하였는데 member가 왼쪽에 있으므로 LEFT의 대상은 member가 되고, member 테이블의 모든 데이터가 포함된다.

member 테이블의 모든 데이터를 포함하므로, ON M.mem_id = B.mem_id에 해당하지 않는 member 테이블의 데이터, 즉 구매 기록이 없는 회원의 데이터도 모두 포함한다. 이는 전체 회원의 구매 기록을 출력하는 것이다. 위에서 잇지와 오마이걸, 레드벨벳, 우주소녀, 트와이스, 여자친구는 모두 구매 이력은 없지만 member가 LEFT의 대상이므로 데이터가 결과에 포함되었고 prod_name은 비어있는 것을 확인할 수 있다.

<실행>

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

<결과>

이번에는 구매 이력이 없는 회원을 조회하기 위해 B.prod_name IS NULL로 한 번도 물건을 구매하지 않은 회원을 조회하였다.

RIGHT OUTER JOIN

RIGHT OUTER JOIN, RIGHT 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;

<결과>

해당 예시는 앞선 예시를 RIGHT OUTER JOIN을 사용하여 출력한 것이다.

LEFT OUTER JOIN의 쿼리문에서 member와 buy의 순서를 바꿔서 RIGHT OUTER JOIN을 써도 똑같은 결과 출력하는 것을 알 수 있다. 즉 LEFT, RIGHT의 차이는 모든 데이터가 포함되는 테이블이 어떤 것이냐를 결정한다.

FULL OUTER JOIN

FULL OUTER JOIN이란 두 테이블의 모든 데이터를 포함하는 조인이다. 즉 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합이라고 보면 된다.

MySQL에서는 FULL OUTER JOIN을 지원하지 않기 때문에 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 UNION을 사용하여 합해야 한다.

<실행>

SELECT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
LEFT JOIN buy B
ON M.mem_id = B.mem_id
UNION
SELECT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
RIGHT JOIN buy B
ON M.mem_id = B.mem_id;

<결과>

LEFT JOIN과 RIGHT JOIN을 UNION을 이용해 합쳐 FULL OUTER JOIN 결과를 생성했다.

UNION은 UNION 앞에 나온 쿼리의 결과와 UNION 뒤에 나온 쿼리의 결과를 합치는 것으로 중복된 행은 제거한다. 즉 합집합이라고 생각하면 된다. UNION ALL도 존재하는데 UNION에서 중복된 행을 제거하지 않은 것이다.

상호 조인(CROSS JOIN)

상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 것으로 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수이다. 따라서 상호 조인을 카티션 곱(cartesian product)이라고도 한다.

<실행>

SELECT * FROM buy CROSS JOIN member;

<결과>

회원 테이블에 10개 행, 구매 테이블에 12개 행이 존재하므로 총 120개의 결과 생성된 것을 확인할 수 있다. 상호 조인은 왼쪽 테이블과 오른쪽 테이블의 모든 데이터를 결합하는 것이므로 ON절을 사용할 수 없다.

조인 결과의 순서는 랜덤이기에 의미가 없으며, 상호 조인의 주 용도는 테스트를 위해 대용량의 데이터 생성하는데 사용한다.

CREATE TABLE cross_table
SELECT *
FROM sakila.actor
CROSS JOIN world.country;

만약 대용량의 테이블을 생성하고 싶다면 CREATE TABLE ~ SELECT문을 사용하자.

자체 조인(SELF JOIN)

자체 조인이란 자신이 자신과 조인하는 것으로 자기 자신, 즉 1개의 테이블을 사용한다. 별도의 문법 존재하지 않으며, 1개로 테이블로 조인할 시 자체 조인이다. 그렇다면 자체 조인은 언제 사용할까?

직원(emp) - PK직속 상관(manager)사내 연락처(phone)
대표없음0000
영업이사대표1111
관리이사대표2222
정보이사대표3333
영업과장영업이사1111-1
경리부장관리이사2222-1
인사부장관리이사2222-2
개발팀장정보이사3333-1
개발주임정보이사3333-1-1

위 테이블에은 직원과 직원을 관리하는 직속 상관, 그리고 사내 연락처가 있는데 위 테이블의 직속 상관의 연락처 또한 확인하려면 어떻게 해야할까? 이럴 때 필요한 것이 자체 조인이다.

예를 들어 관리이사는 직원이므로 직원 열에 속하나 동시에 경리부장과 인사부장의 상관이어서 직속 상관 열에도 속한다. 만약 경리부장의 직속상관인 관리이사의 사내 연락처를 알고 싶다면 EMP 열과 MANAGER 열을 조인해야 한다.

SELECT 열 이름
FROM 테이블 이름 별칭A
	INNER JOIN 테이블 이름 별칭B
	ON 조인 조건
[WHERE 검색 조건]

자체 조인에서 테이블은 1개지만 다른 별칭을 사용하여 구분한다.

<실행>

USE market_db;
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 = '경리부장';

<결과>

위 쿼리를 실행하여 경리부장의 직속상관의 정보와 연락처를 조회해보도록 하자.

출처

  • 혼자 공부하는 SQL(우재남 저, 한빛미디어)
profile
prefer
기술적 의사결정에 객관성을 가지는 Back-End 개발자 이선호입니다.
이전 포스트

SQL 고급 문법 (1)

다음 포스트

SQL 고급 문법 (3)

0개의 댓글