[Oracle] 오라클 #16, 조인(join) 개념과 종류, 예제 및 응용

조인, Join

조인을 하기 이전

직원 테이블 + 담당 프로젝트 정보

create table tblStaff

(

    seq number primary key, --직원번호(PK)

    name varchar2(30) not null, --직원명

    salary number not null, --급여

    address varchar2(300) not null, --주소

    projectname varchar2(100) null --해당이 담당 중인 프로젝트명

);

insert into tblStaff (seq, name, salary, address, projectname) values (1, '홍길동', 250, '서울시', '홍콩 수출');

insert into tblStaff (seq, name, salary, address, projectname) values (2, '아무개', 230, '부산시', 'TV 광고');    

insert into tblStaff (seq, name, salary, address, projectname) values (3, '하하하', 210, '서울시', '매출 분석');

>>> 이러한 단순 테이블 구조는 한명당 하나의 프로젝트만이 가능하다.

조인을 이용

정책 : 직원 1명이 여러 개의 프로젝트를 담당하는게 가능하다.

직원 테이블

create table tblStaff

(

    seq number primary key, --직원번호(PK)

    name varchar2(30) not null, --직원명

    salary number not null, --급여

    address varchar2(300) not null --주소

    --projectSeq number null --프로젝트번호 이렇게되면 역시나 1명당 1개의 프로젝트밖에 못가지므로 컬럼 삭제!

);

프로젝트 테이블

create table tblProject

(

    seq number primary key, --프로젝트번호(PK)

    projectname varchar2(100) null, --해당이 담당 중인 프로젝트명

    staffSeq number null --직원번호

);

insert into tblStaff (seq, name, salary, address) values (1, '홍길동', 250, '서울시');

insert into tblStaff (seq, name, salary, address) values (2, '아무개', 230, '부산시');

insert into tblStaff (seq, name, salary, address) values (3, '하하하', 210, '서울시');

insert into tblProject (seq, projectname, staffSeq) values (1, '홍콩 수출', 1);

insert into tblProject (seq, projectname, staffSeq) values (2, 'TV 광고', 2);

insert into tblProject (seq, projectname, staffSeq) values (3, '매출 분석', 2);

insert into tblProject (seq, projectname, staffSeq) values (4, '노조 협상', 1);

insert into tblProject (seq, projectname, staffSeq) values (5, '대리점 분양', 3);

select * from tblStaff;

select * from tblProject;

tblStaff(기본 테이블, 부모 테이블) + tblProject(참조 테이블, 자식 테이블) : 두 테이블이 관계를(Relationship) 맺고 있다.

Oracle은 RDBMS(Relational Databse Management System)이다.

관계를 맺고 있는 2개의 테이블의 데이터를 조작하면.. 생기는 일들..

 1. 부모 테이블의 조작

      a. 추가

      b. 수정

      c. 삭제

 2. 자식 테이블의 조작

      a. 추가

      b. 수정

      c. 삭제

상황 1. 신입 사원 입사 -> 신규 프로젝트 시작(담당자 배정)

1.a. 신입 사원 추가(O)

insert into tblStaff (seq, name, salary, address) values (4, '호호호', 190, '부천시');

1.b. 신규 프로젝트 추가(O)

insert into tblProject (seq, projectname, staffSeq) values (6, '자재 매입', 4);

1.c. 신규 프로젝트 추가(O -> X)

--ORA-02291: integrity constraint (HR.TBLPROJECT_STAFFSEQ_FK) violated - parent key not found

insert into tblProject (seq, projectname, staffSeq) values (7, '고객 유치', 5);

>> 즉, staffSeq에 해당하는 직원의 키가 없으므로 tblProject 테이블의 staffSeq 컬럼에는 아무값이나 넣을 수 없고, tblStaff 컬럼에 있는 PK에 값이 있어야 삽입할 수 있다.

상황 2. '홍길동' 퇴사

2.a. '홍길동' 삭제(O -> X)

--ORA-02292: integrity constraint (HR.TBLPROJECT_STAFFSEQ_FK) violated - child record found

delete from tblStaff where name = '홍길동';

delete from tblStaff where seq = 1;

>> 홍길동이라는 사람이 아직 어떤 프로젝트를 맡고 있기 때문에 해당 프로젝트를 다른사람으로 변경하고 삭제해야함.

2.b. 남아있는 직원들에게 '홍길동'의 업무를 위임.

update tblProject set staffSeq = 2  where staffSeq = 1;

2.c. 미련없이 '홍길동' 퇴사

delete from tblStaff where name = '홍길동';

부모 테이블 + 자식 테이블 : 논리적 관계(사람이 관리) > 약한 결합

부모 테이블 + 자식 테이블 : 물리적 관계(DBMS가 관리) > 강한 결합 > 제약 사항 추가

외래키, Foreign Key

- 참조 관계에 있는 두 테이블간에 연결 고리 역할을 하는 컬럼들이 있다. 그 연결 고리 역할의 컬럼값들을 항상 유효하게 유지시켜주는 제약 사항

직원 테이블

create table tblStaff

(

    seq number primary key, --직원번호(PK)

    name varchar2(30) not null, --직원명

    salary number not null, --급여

    address varchar2(300) not null --주소

);

프로젝트 테이블

create table tblProject

(

    seq number primary key, --프로젝트번호(PK)

    projectname varchar2(100) null, --해당이 담당 중인 프로젝트명

    staffSeq number constraint tblproject_staffseq_fk references tblStaff(seq) not null  --직원번호(FK) 얘가 외래키

);

조인 관련한 예제 테이블들 정의

비디오 대여점

장르 테이블

create table tblGenre

(

    seq number primary key, --장르번호(PK)

    name varchar2(30) not null, --장르명

    price number not null, --대여 가격

    period number not null --대여 기간

);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '액션',1500,2);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '에로',1000,1);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '어린이',1000,3);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '코미디',2000,2);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '멜로',2000,1);

INSERT INTO tblGenre VALUES (genreSeq.NEXTVAL, '기타',1800,2);

비디오 테이블

create table tblVideo

(

    seq number primary key, --비디오번호(PK)

    name varchar2(100) not null, --제목

    qty number not null, --보유 수량

    company varchar2(50) null, --제작사

    director varchar2(50) null, --감독

    major varchar2(50) null, --주연배우

    genre number references tblGenre(seq) not null --장르번호(FK)

);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '영구와 땡칠이',5,'영구필름','심영래','땡칠이',3);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '어쭈구리',5,'에로 프로덕션','김감독','박에로',2);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '털미네이터',3,'파라마운트','James','John',1);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '육복성',3,'대만영화사','홍군보','생룡',4);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '뽀뽀할까요',6,'뽀뽀사','박감독','최지후',5);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '우정과 영혼',2,'파라마운트','James','Mike',5);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '주라기 유원지',1,NULL,NULL,NULL,1);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '타이거 킹',4,'Walt','Kebin','Tiger',3);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '텔미 에브리 딩',10,'영구필름','강감독','심으나',5);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '동무',7,'부산필름','박감독','장동근',1);

INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (videoSeq.NEXTVAL, '공동경쟁구역',2,'뽀뽀사','박감독','이병흔',1);

회원 테이블

create table tblMember

(

    seq number primary key, --회원번호(PK)

    name varchar2(20) not null, --회원명

    grade number(1) not null, --회원 등급(1,2,3)

    byear number(4) not null, --생년

    tel varchar2(15) not null, --연락처

    address varchar2(300) null, --주소

    money number not null --예치금

);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '김유신',1,1970,'123-4567','12-3번지 301호',10000);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '강감찬',1,1978,'111-1111','777-2번지 101호',0);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '유관순',1,1978,'222-2222','86-9번지',20000);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '이율곡',1,1982,'333-3333',NULL,15000);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '신숙주',1,1988,'444-4444','조선 APT 1012호',0);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '안중근',1,1981,'555-5555','대한빌라 102호',1000);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '윤봉길',1,1981,'666-6666','12-1번지',0);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '이순신',1,1981,'777-7777',NULL,1500);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '김부식',1,1981,'888-8888','73-6번지',-1000);

INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (memberSeq.NEXTVAL, '박지원',1,1981,'999-9999','조선 APT 902호',1200);

대여 테이블

create table tblRent

(

    seq number primary key, --대여번호(PK)

    member number references tblMember(seq) not null, --대여한 회원번호(FK)

    video number references tblVideo(seq) not null, --대여한 비디오번호(FK)

    rentdate date default sysdate not null, --대여날짜

    retdate date null, --반납날짜

    remart varchar2(500) --비고

);

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 1,1,'2007-01-01',NULL);

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 2,2,'2007-02-02','2001-02-03');

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 3,3,'2007-02-03',NULL);

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 4,3,'2007-02-04','2001-02-08');

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 5,5,'2007-02-05',NULL);

INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (rentSeq.NEXTVAL, 1,2,'2007-02-10',NULL);

시퀀스 객체

create sequence memberSeq;

create sequence genreSeq;

create sequence videoSeq;

create sequence rentSeq;

SELECT * FROM tblgenre; -- 6

SELECT * FROM tblvideo; -- 11

SELECT * FROM tblmember; -- 10

SELECT * FROM tblrent; -- 6

조인, Join

- 2개(1개) 이상의 테이블의 내용을 한번에 가져와 1개의 결과셋을 만드는 작업

- 분리되어 있는 2개 이상의 테이블을 1개로 만드는 작업

- 2개 이상의 테이블이 서로 관계가 있어야 한다.(관계 테이블끼리 조인하는 경우 X)

조인의 종류(ANSI SQL)

1. 단순 조인, Cross Join

2. 내부 조인, Inner Join

3. 외부 조인, Outer Join

4. 셀프 조인, Self Join

1. 단순조인, 크로스 조인

select * from tblCustomer; --3명

select * from tblSales; --9건

-- 27건 ( 3 x 9 )

select * from tblCustomer, tblSales; -- Oracle 표현

select * from tblCustomer cross join tblSales; -- ANSI 표현

2. 내부 조인

: 단순 조인에서 유효한 레코드만을 취하는 조인

: 부모 테이블의 PK와 자식 테이블의 FK가 동일한 레코드만을 취하는 조인

select 컬럼리스트 

from 테이블A 

(탭한칸) inner join 테이블B on 테이블A.컬럼명(PK) = 테이블B.컬럼명(FK);

-- inner join의 결과 > 자식 테이블 레코드 수 > 9건

구매내역과 해당 손님의 정보를 가져오시오.

select * from tblCustomer

        inner join tblSales on tblCustomer.seq = tblSales.customer; --부모 테이블 join 자식 테이블(권장)

select * from tblSales

        inner join tblCustomer on tblSales.customer = tblCustomer.seq; --자식 테이블 join 부모 테이블

-- ORA-00918: column ambiguously defined  

>> 두개의 테이블을 조인한 경우 seq 같은 컬럼은 이름이 겹칠수가 있음. 그래서 앞에 테이블이름을 써서 접근해야 에러안남.

-- > 테이블명.컬럼명

select tblSales.seq, tblCustomer.seq from tblCustomer

        inner join tblSales

                on tblCustomer.seq = tblSales.customer;

표준(ANSI ***) SQL inner 조인 방식

select * from tblCustomer

        inner join tblSales

                on tblCustomer.seq = tblSales.customer;

오라클 방식

select * from tblCustomer, tblSales

        where tblCustomer.seq = tblSales.customer;

조인 사용 시 절대(되도록) 하면 안되는 행동

PK - FK 연결된 관계있는 테이블끼리만 하자

select * from tblStaff;

select * from tblZoo;

select * from tblStaff

        inner join tblZoo

                on tblStaff.seq = tblZoo.leg;  --> seq랑 leg랑 데이터 타입은 같다 치더라도 아무 연관(relation)이 없는데 왜 조인함? 이런걸 당연히 피해야함.

노트(tblSales)를 사간 회원의 연락처(tblCustomer)?

--1. 서브쿼리

select customer from tblSales where item = '노트';

    

select tel from tblCustomer where seq = 2;

select name, tel from tblCustomer

    where seq = (select customer from tblSales where item = '노트');

-- 2. 조인

select tblCustomer.name, tblCustomer.tel from tblCustomer

    inner join tblSales on tblCustomer.seq = tblSales.customer

where tblSales.item = '노트';

--서브쿼리를 from 절에 적용하기

select name, tel from (select * from tblCustomer

    inner join tblSales on tblCustomer.seq = tblSales.customer)

where item = '노트';

-- 일일히 컬럼명앞에 테이블명을 붙이기가 귀찮다. >  간략하게 기재  > Table Alias(별칭)

select c.name, c.tel --4. 

    from tblCustomer c inner join tblSales s on c.seq = s.customer --2.

where s.item = '노트';

select * from tblCustomer; --3명

select * from tblSales; --9건

-- 고객 1명 가입

insert into tblCustomer values (4, '호호호', '010-9876-5432', '서울시');

****

- 내부 조인은 부모 테이블의 레코드가 자식 테이블에 참조되지 않으면 그 부모 레코드는 결과셋에서 제외가 된다

>> 즉, 저 위의 고객 4번은 아직 물건을 구입하지 않았다. 그래서 구매목록테이블에 저사람의 데이터가 없다.(4번이 없다는 뜻.) 그래서 구매목록에 저사람의 구매목록은 뜨지 않는다는 말이다. 

- 쇼핑몰에서 적어도 1회 이상(*****) 구매이력이 있는 고객의 정보와 구매 이력을 가져오시오.

- 부모 테이블과 자식 테이블 모두 동시에 존재하는 레코드만 가져온다.

select * from tblCustomer c inner join tblSales s on c.seq = s.customer;

-- 쇼핑몰에서 구매이력과 상관없이 모든 구매 이력과 고객 정보를 가져오되, 

-- 구매 이력 없는 고객의 구매 이력 정보는 비어둔 채로 가져오시오.

select * from tblCustomer c left outer join tblSales s on c.seq = s.customer;

3. 외부 조인

- select 컬럼리스트 from 테이블A left[right] outer join 테이블B on c.PK = s.FK;]

- 일반적으로 방향은 부모테이블을 가리킨다.

-- 방향이 자식 테이블 가리키게 되면 내부 조인의 결과와 동일하다.

select * from tblCustomer c

    right outer join tblSales s

        on c.seq = s.customer;

-- 내부 조인

-- 대여 기록과 함께 고객 정보를 가져오시오.(한번도 대여를 하지 않는 고객은 제외)

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;

4. 셀프 조인

- 1개의 테이블을 가지고 조인

- 자신이 자신을 참조하는 키를 소유함(PK + FK)

-- 직원 정보 테이블

create table tblSelf

(

    seq number primary key, --직원번호(PK)

    name varchar2(30) not null, --직원명

    department varchar2(30) null, --부서

    super number references tblSelf(seq) null --직속상사번호(FK)

);

insert into tblSelf values (1, '홍사장', null, null);

insert into tblSelf values (2, '김부장', '영업부', 1);

insert into tblSelf values (3, '이과장', '영업부', 2);

insert into tblSelf values (4, '정대리', '영업부', 3);

insert into tblSelf values (5, '최사원', '영업부', 4);

insert into tblSelf values (6, '박부장', '홍보부', 1);

insert into tblSelf values (7, '김과장', '홍보부', 6);

select * from tblSelf;

-- 직원 정보 + 직속상사 정보

select s2.name as 직원명, s1.name as 상사명 from tblSelf s1 --부모테이블(상사) 

    inner join tblSelf s2 --자식테이블(부하)

        on s1.seq = s2.super;

select s2.name as 직원명, nvl(s1.name, '미정') as 상사명 from tblSelf s1 --부모테이블(상사) 

    right outer join tblSelf s2 --자식테이블(부하)

        on s1.seq = s2.super;