[혼공SQL] 4-2 두 테이블을 묶는 조인

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

📌 조인(join)이란?

두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것

1. 내부 조인

1.1 일대다 관계

• 두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 함
• 데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이며 분리된 테이블은 서로 관계를 맺고 있음

 

1.2 내부 조인의 기본

• 조인은 3개 이상의 테이블로도 할 수 있지만 대부분 2개로 조인함

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>  -- JOIN이라고만 써도 INNER JOIN으로 인식
	ON <조인될 조건>
[WHERE 검색 조건]

 

📌 두 테이블을 내부 조인

USE market_db;
select * from buy
	inner join member
  on buy.mem_id = member.mem_id
where buy.mem_id = 'GRL';

두 개의 테이블을 조인하는 경우, 동일한 열 이름이 존재한다면 테이블_이름.열_이름 형식으로 표기해야 함

 

📌 두 테이블을 내부 조인하는 과정

 

• WHERE 절을 생략하게 된다면?
  • 구매 테이블의 모든 행이 회원 테이블과 결합함

select * from buy
	inner join member
    on buy.mem_id = member.mem_id

 

1.3 내부 조인의 간결한 표현

select buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
from buy
	inner join member
    on buy.mem_id = member.mem_id;

 

• SELECT 절에 buy.mem_id를 mem_id로 하게 되면 오류가 발생함
  • mem_id가 회원 테이블, 구매 테이블에 모두 있기 때문에 어떤 테이블의 컬럼인지 모르기 때문

 

📌 테이블 이름을 간결하게 표현하기 위해 별칭(alias) 사용

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;

▶ 결과는 동일하게 나옴

 

1.4 중복된 결과 1개만 출력하기

• DISTINCT문을 활용

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 사용 x

DISTINCT 사용 O

 

2. 외부 조인

• 내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나오나 외부 조인은 한쪽에만 데이터가 있어도 결과가 나옴

 

2.1 외부 조인의 기본

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
	<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>

 

📌 전체 회원의 구매기록 (구매 기록이 없는 회원의 정보도 함께) 출력

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;

 

• LEFT OUTER JOIN 문 : 왼쪽 테이블(member)의 내용은 모두 출력
• RIGHT OUTER JOIN 문 : 오른쪽 테이블의 내용은 모두 출력

 

2.2 외부 조인의 활용

📌 구매 기록이 없는 회원 데이터 추출

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    -- 물건 이름이 null 값인 데이터
order by M.mem_id;

 

📌 FULL OUTER JOIN

• 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것
• 왼쪽이든 오른쪽이든 한 쪽에 들어 있는 내용이면 출력

 

3. 기타 조인

3.1 상호 조인 (cross join)

• 한 쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
• 상호 조인 결과의 전체 행 개수 = 두 테이블의 각 행의 개수를 곱한 개수
• **카티션 곱(cartesian product)**이라고도 부름
• ex) A 테이블의 첫 행이 B 테이블의 전체 행과 결합

 

• ON 구문을 사용할 수 없음
• 결과는 랜덤으로 조인하기 때문에 의미 없음
• 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때임

 

select * 
from buy 
	cross join member ;

 

3.2 자체 조인

• 자기 자신과 조인하는 것으로 1개의 테이블을 사용함

 

📌 ex> 회사의 조직 관계

위의 조직도를 테이블로 표현해보자.

• 관리이사는 직원이므로 직원 열에 속함
• 동시에 경리부장과 인사부장의 상관이어서 직속 상관 열에도 속함

 

▶ 직원 중 경리부장의 직속상관인 관리이사의 사내 연락처를 알고 싶다면 EMP 열과 MANAGER 열을 조인해야 함

 

 

-- 테이블 생성
create table emp_table (emp char(4), manager char(4), phone varchar(8));

-- 데이터 삽입
insert into emp_table values ('관리이사', '대표', '2222');
insert into emp_table values ('경리부장', '관리이사', '2222-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 = '경리부장';