join.sql

관계형 데이터베이스 시스템이 지양하는 것들

=> 테이블을 다시 수정해야 고쳐지는 것들 > 구조적 문제!!

1. 테이블에 기본키가 없는 상태 > 데이터 조작 불가능 (레코드 식별 불가능)

2. null이 많은 상태의 테이블 > 공간 낭비 + SQL 작업 불편

3. 데이터가 중복되는 상태 > 공간 낭비 + 데이터 조작 문제 발생(일관성 저하)

4. 하나의 속성값이 원자값이 아닌 상태 > 더 이상 쪼개지지 않는 값을 넣어야 한다.

• 실습 코드

-- 직원 정보
-- 직원(번호(PK), 직원명, 급여, 거주지, 담당프로젝트)
create table tblStaff (
    seq number primary key,         -- 직원번호(PK)
    name varchar2(30) not null,     -- 직원명
    salary number not null,         -- 급여
    address varchar2(300) not null, -- 거주지
    project varchar2(300)           -- 담당프로젝트
);

insert into tblStaff (seq, name, salary, address, project)
    values (1, '홍길동', 300, '서울시', '홍콩 수출');
            
insert into tblStaff (seq, name, salary, address, project)
    values (2, '아무개', 250, '인천시', 'TV 광고');
            
insert into tblStaff (seq, name, salary, address, project)
    values (3, '하하하', 350, '의정부시', '매출 분석');
    
-- '홍길동'에게 담당 프로젝트가 1건 추가 > '고객 관리'
-- 홍콩 수출 + 고객 관리

-- 데이터 중복 발생        
insert into tblStaff (seq, name, salary, address, project)
    values (4, '홍길동', 300, '서울시', '고객관리');        

📌 아래와 같은 문제 발생

-- '회원 응대' > 당당자?
select * from tblStaff where project = '회원 응대'; -- 원자값이 아니라서 찾을 수 없음
select * from tblStaff where project like '%회원 응대%'; -- 원하는 회원 응대를 찾기가 힘듬 

테이블을 재구성 해야 함!

-- 직원 정보
-- 직원 (번호(PK), 직원명, 급여, 거주지)
create table tblStaff(
    seq number primary key,         -- 직원번호(PK)
    name varchar2(30) not null,     -- 직원명
    salary number not null,         -- 급여
    address varchar2(300) not null   -- 거주지
); -- 부모 테이블


-- 프로젝트 (번호(PK), 담당 프로젝트, 담당 직원 번호)
-- 자식 테이블부터 만들 수 없음, 부모 테이블을 먼저 만들어야 함
create table tblProject (
    seq number primary key,         -- 프로젝트번호(PK)
    project varchar2(100) not null, -- 프로젝트명
    staff_seq number not null references tblStaff(seq)  -- 담당직원번호(FK)
); -- 자식 테이블

insert into tblStaff (seq, name, salary, address) values (1, '홍길동',300, '서울시');   
insert into tblStaff (seq, name, salary, address) values (2, '아무개',250, '인천시');   
insert into tblStaff (seq, name, salary, address) values (3, '하하하',350, '부산시');   
        
insert into tblProject (seq, project, staff_seq) values (1, '홍콩 수출', 1);      -- 홍길동  
insert into tblProject (seq, project, staff_seq) values (2, 'TV 광고', 2);        -- 아무개 
insert into tblProject (seq, project, staff_seq) values (3, '매출 분석', 3);      -- 하하하
insert into tblProject (seq, project, staff_seq) values (4, '노조 협상', 1);      -- 홍길동
insert into tblProject (seq, project, staff_seq) values (5, '대리점 분양', 2);    -- 아무개

-- 'TV 광고' 담당자?
--  TV 광고 담당자 번호 = 2
--  담당자 번호 아무개 250 인천시
select * from tblProject where project = 'TV 광고';
select * from tblStaff where seq = 2;

select * from tblStaff where seq = (select staff_seq from tblProject where project = 'TV 광고');

---

조인, Join

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

조인의 종류

1. 단순 조인, cross join
2. 내부 조인, inner join  ***
3. 외부 조인, outer join  ***
4. 셀프 조인, self join
5. 전체 외부 조인, full outer join

1. 단순 조인, cross join, 카디션 곱, 데카르트 곱
: 모든 조인의 기본 동작
: A 테이블 X B 테이블
: 쓸모없다. > 가치가 있는 행과 가치가 없는 행이 뒤섞여 있어서...
: 다량 더미 데이터(유효성 낮음) 생성에 사용

• 실습 코드

select * from tblCustomer; --5명
select * from tblSales;    --9건

select * from tblCustomer cross join tblSales;  --ANSI-SQL // 45개
select * from tblCustomer, tblSales; --Oracle 전용

---

2. 내부 조인, inner join

: 단순 조인에서 유효한 레코드만 추출한 조인

    select 컬럼리스트 from 테이블A cross join 테이블B;
    
    select 컬럼리스트 from 테이블A inner join 테이블B on 테이블A.PK = 테이블B.FK;
    
    select 
        컬럼리스트 
    from 테이블A 
        inner join 테이블B 
            on 테이블A.PK = 테이블B.FK;

• 실습 코드

select * from tblCustomer; --5명
select * from tblSales;    --9건

select * from tblCustomer inner join tblSales on tblCustomer.seq = tblSales.cseq; 

📌 주의 사항

--ORA-00918: 열의 정의가 애매합니다
select 
    seq 
from tblCustomer 
    inner join tblSales 
        on tblCustomer.seq = tblSales.cseq; 

select 
    tblCustomer.seq as 고객번호,
    tblSales.seq as 판매번호,
    tblCustomer.name as 고객명 -- 안써도 되는 것도 써주는 것이 좋음
from tblCustomer 
    inner join tblSales 
        on tblCustomer.seq = tblSales.cseq; 
        
select 
    c.seq as 고객번호,
    s.seq as 판매번호,
    c.name as 고객명 -- 안써도 되는 것도 써주는 것이 좋음
from tblCustomer c --테이블 별칭은 외자
    inner join tblSales s
        on c.seq = s.cseq; 

---

3. 외부 조인, outer join

: 내부 조인의 반댓말(x)
: 내부 조인 결과 + a(내부조인에 포함되지 않은 부모 테이블의 나머지 레코드)

   select
            컬럼리스트
        from 테이블A
            inner join 테이블B
                on 테이블A.PK = 테이블B.FK;
                
        select
            컬럼리스트
        from 테이블A
            (left|right)outer join 테이블B
                on 테이블A.PK = 테이블B.FK;
                        

• 실습 코드

-- 내부 조인
--> 물건을 한번이라도 구매한 이력이 있는 고객의 정보와 구매정보를 가져오세요
select * from tblCustomer c
    inner join tblSales s
        on c.seq = s.cseq; --9개

-- 외부 조인
--> 물건을 한번이라도 구매한 이력이 있는 고객의 정보와 구매정보를 가져오세요
--> 물건을 한번도 구매 안한 고객의 정보까지
select * from tblCustomer c
   left outer join tblSales s
        on c.seq = s.cseq;  --11개

select * from tblCustomer c
   right outer join tblSales s
        on c.seq = s.cseq ; --11개

---

4. 셀프 조인, self join

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

• 실습 코드

create table tblSelf (
    seq number primary key,                      --직원번호(PK)
    name varchar2(30) not null,                  --직원명
    department varchar2(30) not null,            --부서명
    super number null references tblSelf(seq)    --상사번호(FK)
);

-- 직원 명단을 가져오시오. 단, 상사의 이름까지
--1. join
--2. sub query
--3. 계층형 쿼리(오라클 전용)

select 
    s2.name as 직원명,
    s2.department as 부서명,
    s1.name as 상사명
from tblSelf s1             --역할 : 부모테이블 > 상사
    inner join tblSelf s2   --역할 : 자식테이블 > 직원
        on s1.seq = s2.super;

---

5. 전체 외부 조인, full outer join

: 서로 참조하고 있는 관계에서 사용

• 실습 코드

select * from tblStaff;
select * from tblProject;

-- full outer join
-- null인것도 다 출력 
select 
    * 
from tblStaff s
    full outer join tblProject p
        on s.seq = p.staff_seq;