데이터베이스 Join

조인(Join)

Join 이란?
- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법 이다.
- 보통 둘 이상의 행들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.
- 두 개의 테이블을 SELECT문장 안에서 조인 하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유 되어야 한다.

 

Join 방법과 방식
- 조인의 방법 : Equi Join (등가 조인, 내부조인) , Non-Equi Join , Self Join , Outer Join(사원 정보에 상사 정보 넣을때)
- 조인의 방식 : Nested Loop Join, Sort Merge Join, Hash Join

 

- deptno가 서로 중첩이 되어있으면 알아서 인식해서 join 해준다.

- 똑같은 이름을 알아서 자동적으로 조인하기에 natural_join은 위험요소가 생길 수 있다. (편안함과 위험을 등가교환..)

- 생략되는 기능들을 사용하면 sql문을 제 3자가 확인했을때 테이블의 구조를 파악하지 못할 수 있다.

- 그냥 정석대로 INNER JOIN을 사용하는게 에로사항이 발생하지 않고, 마음이 편안합니다.

실무코드는 불확실성 코드를 사용하면 안되기 때문에, 위에 있는 코드의 사용은 지양해야 한다.

 

 

 

 

 

- 크로스 조인(무조건 연결해주는 것으로 조건이 없다)

 

 

 

테이블과 테이블의 결과가 크로스로 조인된다.

dept는 pk인데 sawon 테이블의 deptno는 fk가 된다.

fk의 값이 pk의 값과 같으면 레이블이 결합 상태가 된다.

두 개의  테이블을 연결해서 사용하기 위해서 join을 사용한다.

select * from sawon s, dept d

where s.deptno = d.deptno

 

.

 

Inner join(equi-join)

 

2. inner join(equi-join) : A.컬럼=B.컬럼 일치하는 행끼리 결합
select * from sawon,dept
where sawon.deptno = dept.deptno;

[ANSI-92 SQL] [테이블 A inner join 테이블 B] on 조인조건
select * from sawon s inner join dept d on s.deptno=d.deptno
select * from sawon inner join dept on sawon.deptno=dept.deptno

 

- 위와 같은 구조의 코드이지만, where 문안에 inner join에 대한 조인조건을 넣으면 구분과 코드 가독성이 떨어진다.

select * from sawon,dept
where sawon.deptno = dept.deptno -- 조인조건(where절이 굉장히 지저분해지고 코드 구분이 어려워진다)
and dept.deptno<>10              -- 일반 필터링 조건
and sajob<>'부장';

 

- 위와 다르게 inner join의 조건은 따로 넣어주고, where문의 조건은 따로 넣어주어야 가독성이 좋아진다.

[ANSI-92 SQL] [테이블 A inner join 테이블 B] on 조인조건
: 조인조건 일반 필터조건을 구분해서 작성할 수 있다.
select * from sawon s inner join dept d on s.deptno=d.deptno
where d.deptno<>10 and sajob<>'부장'

 

 

inner join의 view 생성

 

 

- inner join의 view를 생성할 때 중복 명칭이 들어가면 오류가 생긴다. 아래는 오류코드

create or replace view sawon_dept_view
as
select * from sawon s inner join dept d on s.deptno=d.deptno
where d.deptno<>10 and sajob<>'부장'

 

- 이렇게 d.deptno를 제외하고 s.deptno만 포함시켜주면 정상적으로 view 생성이 완료된다.

create or replace view sawon_dept_view
as
select
s.*,dname,loc -- view로 만들게 되면 table은 중복 명칭이 되면 안된다.
from sawon s inner join dept d on s.deptno=d.deptno
where d.deptno<>10 and sajob<>'부장'

 

- deptno의 명칭이 테이블에서 중복이 되기 때문에, d.deptno as deptno2로 이름을 수정해서 정상적인 뷰 생성

create or replace view sawon_dept_view
as
select
s.*,d.deptno as deptno2,dname,loc -- view로 만들게 되면 table은 중복 명칭이 되면 안된다.
from sawon s inner join dept d on s.deptno=d.deptno
where d.deptno<>10 and sajob<>'부장'

 

 

 

 

 

 

Self Join(셀프조인)

 

- 셀프조인을 구성할 때는, 아래와 같이 sawon s1에 들어갈 항목, sawon s2에 들어갈 항목을 구분지어서 하나하나 쌓아 올리는 느낌으로 만들어낸다.

3. self join : 자신의 테이블과 연결시키는 조인
select * from sawon
사번 사원직위 사원명  상사사번 상사직위 상사명
   sawon s1              sawon s2
select
   s1.sabun 사번,s1.sajob 직위,s1.saname 사원명,
   s2.sabun 상사사번,s2.sajob 상사직위,s2.saname 상사명
from sawon s1,sawon s2
where s1.samgr = s2.sabun -- samgr과 sabun이 동일한 것
order by s1.sabun -- 오름차순

 

- 위와 같은 경우로 만들때 테이블 명칭도 중복이 되지 않기 때문에, view로 생성할 때에도 문제없이 적절하게 생성할 수 있다.

 

- 아래와 같이 where문을 명시하기전에 출력하면 sawon1과 sawon2가 전부 출력이 되는데,

s1.samgr = s2.sabun을 where문의 조건으로 달아주게 되면, 맥라이언은 19번 선임과 최명길과 관련이 있기 때문에,

8번을 출력할 때는 저 한 줄을 가져오게 될 것이다.

 

- where문의 조건을 달아놓으니 맥라이언과 최명길이 함께 묶여서 출력이 된다.

 

- 안씨집안 SQL문으로 출력하는 방법

[ANSI-92 SQL]
select
   s1.sabun 사번,s1.sajob 직위,s1.saname 사원명,
   s2.sabun 상사사번,s2.sajob 상사직위,s2.saname 상사명
from sawon s1 inner join sawon s2 on s1.samgr = s2.sabun
order by s1.sabun

 

 

- 위의 항목들은 samgr가 없는 그러니까 선임이 없는 사원들의 목록(null인 값)은 출력이 되지 않았다.

select * from sawon 
where samgr is null

 

- 그래서 outer join을 사용하게 된다. (null 값 또한 모두 테이블에 출력하고 싶을때는 outer join 사용 간단하져??)

 

 

 

 

 

Outer Join

 

4. outer join -- left는 왼쪽 테이블, right는 오른쪽 테이블
A left  outer join B on 연결조건 : A(all) - B(조건에 만족하는행)
A right outer join B on 연결조건 : A(조건에 만족하는행) - B(all)
A full  outer join B on 연결조건 : A(all) - 조건에 만족하는행 - B(all)

select
   s1.sabun 사번,s1.sajob 직위,s1.saname 사원명,
   s2.sabun 상사사번,s2.sajob 상사직위,s2.saname 상사명
from sawon s1 left outer join sawon s2 on s1.samgr = s2.sabun
order by s1.sabun

 

 

 

 

- Oracle에서만 사용가능한 outer join, 아래에는 MS-SQL server에서 사용가능한 사용명령

- 우리는 그냥 위에 있는 안씨네 SQL로 사용하면 좋을 것 같다. (표준화 되서 사용되는 명령 모델이기 때문에)

-- Oracle 문법 left outer join
select
   s1.sabun 사번,s1.sajob 직위,s1.saname 사원명,
   s2.sabun 상사사번,s2.sajob 상사직위,s2.saname 상사명
from sawon s1,sawon s2
where s1.samgr = s2.sabun(+) -- s1.samgr의 행은 모두 출력시키겠다. (만족하는 행은 없는데, 왼쪽을 위해서 행을 추가시켜줄께)
order by s1.sabun

cf) MS-SQL Server
where s1.samgr*=s2.sabun

-- full outer join(위치에 상관없이 모든 데이터가 출력)
select * from
   dept d full outer join sawon s on d.deptno = s.deptno

 

 

 

 

- 예시

 

- TIP : Inner join을 사용할지 left outer join을 사용할지 애매하다면 left outer join 사용

-  Inner join = 모든 항목이 중첩되고, 빠짐없이 정렬되면 사용한다.

- 2개의 테이블을 join 시키는 예제

-- Q1. 다음과 같은 정보 조회(사원이 관리하는 고객정보)
-- 사원정보는 모두 출력
사번 사원명  고객번호 고객명
sawon s      gogek g
select
   s.sabun 사번,s.saname 사원이름,
   g.gobun 고객번호,g.goname 고객이름
from sawon s left outer join gogek g on s.sabun = g.godam
order by s.sabun,gobun

 

 

- 3개의 테이블을 join 시키는 예제

사번 사원명  부서명    고객번호 고객명
   sawon s    dept d      gogek g
select
   s.sabun 사원번호,s.saname 사원이름,d.deptno 부서번호,
   g.gobun 고객번호,g.goname 고객성명,g.godam 고객담당자
from sawon s inner join dept d on s.deptno = d.deptno -- (1)+(2)
     left outer join gogek g on s.sabun = g.godam     -- (1,2)+(3)
order by sabun

 

 

- 5개의 테이블을 join 시키는 예제

사번 직위 사원명   부서명 고객번호 고객명   상사번호 상사직위 상사명 상사고객번호 상사고객명
   sawon s1        dept d    gogek g1          sawon s2                 gogek g2
            (1)           (2)           (3)                   (4)
select
   s1.sabun 사원번호,s1.sajob 직위,s1.saname 사원이름,
   d.dname 부서명, g1.gobun 고객번호, g1.goname 고객명,
   s2.sabun 상사번호, s2.sajob 상사직위, s2.saname 상사명,
   g2.gobun 상사고객번호, g2.goname 상사고객명
from sawon s1 inner join dept d on s1.deptno = d.deptno
   left outer join gogek g1 on s1.sabun = g1.godam
   left outer join sawon s2 on s1.samgr = s2.sabun
   left outer join gogek g2 on s2.sabun = g2.godam
order by sabun

 

 

 

join할 때 테이블이 수많은 경우 쉽게 해결하는 방법

 

- join 하는게 어렵다면, selet * from 테이블명a과 테이블명b를 보고 일치하는 값이 어디부분인지를 파악하자.

 

 

- 안씨 SQL 사용안할경우 코드의 가독성이 안씨 SQL보다 가독성이 떨어진다.

-- 안씨 SQL 사용안할경우
select
   s1.sabun 사원번호,s1.sajob 직위,s1.saname 사원이름,
   d.dname 부서명, g1.gobun 고객번호, g1.goname 고객명,
   s2.sabun 상사번호, s2.sajob 상사직위, s2.saname 상사명,
   g2.gobun 상사고객번호, g2.goname 상사고객명
from sawon s1,dept d,gogek g1,sawon s2,gogek g2
where s1.deptno = d.deptno
   and s1.sabun = g1.godam(+)
   and s1.samgr = s2.sabun(+)
   and s2.sabun = g2.godam(+)
order by 사원번호,상사고객번호