조인, Join

• (서로 관계를 맺은) 2개 이상의 테이블을 1개의 결과셋으로 만드는 기술

조인의 종류

1. 단순 조인, CROSS JOIN

• 카티션곱(데카르트곱)
• A 테이블 X B 테이블
• select 컬럼리스트 from 테이블A CROSS JOIN 테이블B;
• 가치있는 행과 가치없는 행이 섞여 있어 별로 쓸모없다.
• 더미데이터 생성용

SELECT * FROM tblcustomer CROSS JOIN tblsales;	-- ANSI-SQL cross join 구문
SELECT * FROM tblcustomer , tblsales;			-- Oracle 전용 cross join 구문

2. 내부 조인, INNER JOIN ***

• 두 개 이상의 테이블에서 일치하는 행만을 반환한다.
• 주로 관련된 데이터를 연결하는 데 사용된다.
• select 컬럼리스트 from 테이블A INNER JOIN 테이블B ON 테이블A.PK = 테이블B.FK;

-- 직원 테이블과 프로젝트 테이블을 조인
-- 직원이 담당하는 프로젝트를 조회할 수 있다.
SELECT 
	s.seq,
	s.name,
	p.seq,
	p.project
FROM tblstaff s INNER JOIN tblproject p ON s.seq = p.staff_seq
ORDER BY p.seq;

-- 직원 + 부서 + 도시 + 국가 + 대륙
SELECT 
	e.first_name ||' '|| e.last_name AS 직원명,
	d.department_name AS 부서명,
	l.city AS 도시명,
	c.country_name AS 국가명,
	r.region_name AS 대륙명,
	j.job_title AS 직업명
FROM employees e
	INNER JOIN departments d
		ON e.department_id = d.department_id
			INNER JOIN locations l
				ON l.location_id = d.location_id
					INNER JOIN countries c
						ON c.country_id = l.country_id
							INNER JOIN  regions r
								ON r.region_id = c.region_id
									INNER JOIN jobs j
										ON j.job_id = e.job_id;

3. 외부 조인, OUTER JOIN ***

• 내부 조인 결과 + 내부 조인에 포함되지 않았던 부모 테이블의 나머지 레코드를 합하는 조인
• LEFT OUTER JOIN : 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환
• RIGHT OUTER JOIN : 왼쪽 테이블에서 일치하는 행과 오른쪽 테이블의 모든 행을 반환.
• select 컬럼리스트 from 테이블A lftf/right outer JOIN 테이블B ON 테이블A.PK = 테이블B.FK;

-- 담당 프로젝트의 유무와 상관없이 모든 직원 조회
SELECT *
FROM tblstaff s
	LEFT OUTER JOIN tblproject p
		ON s.seq = p.staff_seq;

-- 한번 이상 대여했던 회원과 대여 내역
SELECT *
FROM tblmember m
	INNER JOIN tblrent r
		ON m.seq = r.MEMBER;

-- 대여를 한번도 하지 않은 고객 명단
SELECT *
FROM tblmember m
	LEFT OUTER JOIN tblrent r
		ON m.seq = r.MEMBER
			WHERE r.seq IS null;

4. 셀프 조인, SELF JOIN

• 1개의 테이블을 사용하는 조인
• 테이블이 자기 스스로와 관계를 맺는 경우

-- 직원과 그 직원의 상사를 같이 조회
-- 직원 테이블 
CREATE TABLE tblSelf (
	seq NUMBER PRIMARY KEY,						--직원번호(PK)
	name varchar2(30) NOT NULL,					--직원명
	departments varchar2(30) NOT NULL,			--부서명
	super NUMBER NULL REFERENCES tblself(seq)	--상사번호(FK)
);


-- outer join
SELECT 
	b.name AS 직원명,
	b.departments AS 부서명,
	a.name AS 상사이름
FROM tblself a						--부모테이블 역할
	RIGHT outer JOIN tblself b			--자식테이블 역할
		ON a.seq = b.super;

-- self join
SELECT
	name AS 직원명,
	departments AS 부서명,
	(SELECT name FROM tblself WHERE seq = a.super) AS 상사명
FROM tblself a;

5. 전체 외부 조인, FULL OUTER JOIN

• 양쪽 테이블에서 일치하는 행과 각각의 테이블에서 일치하지 않는 모든 행을 반환
• 서로 참조하고 있는 관계에서 사용

-- 커플 + 남자 솔로 + 여자 솔로.
SELECT 
	m.name,
	w.name
FROM tblmen m
	FULL OUTER JOIN tblwomen w
		ON m.name = w.couple;