DATABASE STEP 21 - JOIN

★ 조인(Join)

• (서로 관계를 맺은) 2개(1개) 이상의 테이블을 사용해서, 1개의 결과셋을 만드는 연산
• 테이블 A + 테이블 B = 테이블 C 
• 조인의 종류
  
  • 1. 단순 조인, Cross Join
  • 2. 내부 조인, Inner Join
  • 3. 외부 조인, Outer Join
  • 4. 셀프 조인, Self Join
  • 5. 전체 외부 조인, Full Outer Join

 

★ 조인에 용이한 더미데이터 테이블 생성(고객 <-> 판매테이블)

• 고객 <-> 판매 테이블
• 1. 고객 테이블 (부모 테이블)

create table tblCustomer (
    seq number primary key,     -- 고객번호(PK)
    name varchar2(30) not null,     -- 고객명
    tel varchar2(15) not null,          -- 연락처
    address varchar2(100) not null  -- 주소
);

-- 고객 데이터
insert into tblcustomer (seq, name, tel, address)
      values (1, '홍길동', '010-1234-5678', '서울시');
insert into tblcustomer (seq, name, tel, address)
      values (2, '아무게', '010-3333-4444', '인천시');
insert into tblcustomer (seq, name, tel, address)
      values (3, '하하하', '010-5555-6666', '부산시');

• 2. 판매내역 테이블

create table tblSales (
    seq number primary key,                                  -- 판매번호(PK) 
    item varchar2(50) not null,                                 -- 상품명
    qty number not null,                                        -- 수량
    regdate date default sysdate not null,                  -- 판매날짜
    cseq number not null references tblCustomer(seq) -- 고객번호(FK)
);

insert into tblsales (seq, item, qty, cseq) values (1, '전화기', 1, 1);
insert into tblsales (seq, item, qty, cseq) values (2, '다이어리', 3, 2);
insert into tblsales (seq, item, qty, cseq) values (3, '노트', 10, 2);
insert into tblsales (seq, item, qty, cseq) values (4, '볼펜', 20, 3);
insert into tblsales (seq, item, qty, cseq) values (5, '지우개', 15, 3);
insert into tblsales (seq, item, qty, cseq) values (6, '마우스', 5, 1);
insert into tblsales (seq, item, qty, cseq) values (7, '키보드', 2, 3);
insert into tblsales (seq, item, qty, cseq) values (8, '모니터', 1, 2);
insert into tblsales (seq, item, qty, cseq) values (9, '선풍기', 2, 1);

 

★ 조인에 용이한 더미데이터 테이블 생성(비디오 판매 관련 테이블)

• 장르 <-> 비디오 <-> 대여 <-> 고객
• 1. 장르 테이블

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 (1, '액션',1500,2);
INSERT INTO tblGenre VALUES (2, '에로',1000,1);
INSERT INTO tblGenre VALUES (3, '어린이',1000,3);
INSERT INTO tblGenre VALUES (4, '코미디',2000,2);
INSERT INTO tblGenre VALUES (5, '멜로',2000,1);
INSERT INTO tblGenre VALUES (6, '기타',1800,2);

• 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 not null references tblGenre(seq)  --장르 번호(FK)
);

-- 비디오 데이터
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (1, '영구와 땡칠이',5,'영구필름','심영래','땡칠이',3);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (2, '어쭈구리',5,'에로 프로덕션','김감독','박에로',2);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (3, '털미네이터',3,'파라마운트','James','John',1);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (4, '육복성',3,'대만영화사','홍군보','생룡',4);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (5, '뽀뽀할까요',6,'뽀뽀사','박감독','최지후',5);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (6, '우정과 영혼',2,'파라마운트','James','Mike',5);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (7, '주라기 유원지',1,NULL,NULL,NULL,1);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (8, '타이거 킹',4,'Walt','Kebin','Tiger',3);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (9, '텔미 에브리 딩',10,'영구필름','강감독','심으나',5);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (10, '동무',7,'부산필름','박감독','장동근',1);
INSERT INTO tblVideo (seq, Name, qty, Company, Director, Major, Genre) VALUES (11, '공동경쟁구역',2,'뽀뽀사','박감독','이병흔',1);

• 3. 대여 테이블

create table tblRent (
    seq number primary key,                                             -- 대여 번호(PK)
    member number not null references tblMember(seq),         -- 회원 번호(FK)
    video number not null references tblVideo(seq),                -- 비디오 번호(FK)
    rentdate date default sysdate not null,                            -- 대여날짜 
    retdate date null,                                                       -- 반납 날짜 
    remark varchar2(500) null                                            -- 비고
);

-- 대여 데이터
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (1, 1,1,'2022-01-01',NULL);
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (2, 2,2,'2022-02-02','2022-02-03');
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (3, 3,3,'2022-02-03',NULL);
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (4, 4,3,'2022-02-04','2022-02-08');
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (5, 5,5,'2022-02-05',NULL);
INSERT INTO tblRent (seq, member, video, Rentdate, Retdate) VALUES (6, 1,2,'2022-02-10',NULL);

• 4. 회원 테이블

create table tblMember (
    seq number primary key,                 -- 고객 번호(PK)
    name varchar2(30) not null,              -- 고객 명
    grade number(1) not null,                -- 고객 등급
    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 (1, '김유신',1,1970,'123-4567','12-3번지 301호',10000);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (2, '강감찬',1,1978,'111-1111','777-2번지 101호',0);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (3, '유관순',1,1978,'222-2222','86-9번지',20000);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (4, '이율곡',1,1982,'333-3333',NULL,15000);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (5, '신숙주',1,1988,'444-4444','조선 APT 1012호',0);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (6, '안중근',1,1981,'555-5555','대한빌라 102호',1000);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (7, '윤봉길',1,1981,'666-6666','12-1번지',0);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (8, '이순신',1,1981,'777-7777',NULL,1500);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (9, '김부식',1,1981,'888-8888','73-6번지',-1000);
INSERT INTO tblMember (seq, Name,Grade,Byear,Tel,address,Money) VALUES (10, '박지원',1,1981,'999-9999','조선 APT 902호',1200);

 

■ 1. 단순 조인(Cross Join)

• 카티션곱, 데카르트곱
• A 테이블 레코드 갯수 * B 테이블 레코드 갯수 = 결과셋 레코드 갯수
• A 테이블 컬럼 갯수 + B 테이블 컬럼 갯수 = 결과셋 컬럼 갯수
• 쓸모없다. > 가치 있는 행과 가치 없는 행이 뒤섞여 있어서...
• 개발용, 테스트용 > 유효성과 상관없이 다량의 데이터를 만들 때...
• 사용 양식

select 컬럼리스트 from 테이블 A cross join 테이블 B

• 사용 ex)

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

select * from tblCustomer cross join tblSales; -- ANSI-SQL 표준 표기 > 권장
select * from tblCustomer, tblSales;             -- Oracle 전용 표기

 

■ 2. 내부 조인(inner join)

• 단순 조인에서 유효한 레코드만 추출하는 조인
• 사용 양식

select 컬럼리스트 from 테이블 A inner join 테이블 B on 테이블A.컬럼 = 테이블B.컬럼;     -- ANSI-SQL 표기법
select 컬럼리스트 from 테이블A, 테이블B where 테이블A.컬럼 = 테이블B.컬럼;      -- Oracle 표기법
    
select
    컬럼리스트
from 테이블 A
    inner join 테이블 B
        on 테이블A.pk컬럼 = 테이블B.pk컬럼;

• ex1) 고객 테이블과 주문 테이블의 inner 조인

select 
    name, item, c.seq, s.seq
from tblCustomer c
    inner join tblSales s 
        on c.SEQ = s.CSEQ;

• ex2) 진행중인 프로젝트명(tblProject)와 해당 담당직원의 이름(tblStaff)을 가져오시오.

select 
    s.name, p.project
from tblStaff s
    inner join tblProject p
        on s.seq = p.staff_seq;

• ex3) 비디오 제목(tblVideo), 대여 가격(tblGenre)을 가져오시오.

select
    v.name,
    g.price
from tblGenre g
    inner join tblVideo v
        on g.seq = v.genre;

• ex4) 6개의 테이블을 join

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 d.department_id = e.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)

• 내부 조인 결과 + 결과셋에 포함되지 못한 부모 테이블의 나머지 레코드
• 사용 양식

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

• ex1) 한번이라도 구매한 이력이 있는 고객들의 정보와 구매 이력을 가져오시오.

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

• ex2) 구매이력과 상관없이 모든 고객들의 정보를 가져오되, 단 구매 이력이 있으면 그것도 같이 가져오시오.

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

• ex3) 담당 프로젝트가 없는 직원들까지 참조가 없는 부모레코드까지 가져오시오.

select 
    *
from tblStaff s
    left outer join tblProject p
        on s.seq = p.staff_seq;

• ex4) 대여가 한번이라도 된 비디오와 그 대여 내역을 가져오시오.

select
    name, count(rentdate)
from tblVideo v
    left outer join tblRent r
        on v.seq = r.video
            group by name
                order by count(rentdate) desc ;

• ex5) 한번도 대여되지 않은 악성재고 목록을 보이시오.

select
    *
from tblVideo v
    left outer join tblRent r
        on v.seq = r.video
            where rentdate is null; --악성 재고 : 아무도 한번도 안빌려간 비디오

• ex6) 악성고객 : 대여를 한번도 하지 않은 고객들을 보이시오.

select
    *
from tblMember m
    left outer join tblRent r
        on m.seq = r.member
            where rentdate is null;

 

★ 요구사항에 따른 Join 예제

• 1. tblStaff, tblProject. 현재 재직중인 모든 직원의 이름, 주소, 월급, 담당프로젝트명을 가져오시오.

select * from tblstaff;
select * from tblProject;
select 
    s.name, s.address, s.salary, p.project
from tblStaff s
    inner join tblProject p
        on s.seq = p.Staff_seq;

• 2. tblVideo, tblRent, tblMember. '뽀뽀할까요' 라는 비디오를 빌려간 회원의 이름은?

select * from tblVideo;
select * from tblRent;
select * from tblMember;

select
    m.name
from tblVideo v
    inner join tblRent r
        on v.seq = r.video
            inner join tblMember m
                on m.seq = r.member
                    where v.name = '뽀뽀할까요';

• 3. tblStaff, tblProejct. 'TV 광고'을 담당한 직원의 월급은 얼마인가?

select * from tblStaff;
select * from tblProject;

select
    s.name,
    s.salary
from tblStaff s
    inner join tblProject p
        on s.seq = p.Staff_seq
            where p.project = 'TV 광고';

• 4. tblVideo, tblRent, tblMember. '털미네이터' 비디오를 한번이라도 빌려갔던 회원들의 이름은?

select * from tblVideo;
select * from tblRent;
select * from tblMember;

select
    m.name
from tblVideo v
    inner join tblRent r
        on v.seq = r.video
            inner join tblMember m
                on m.seq = r.member
                    where v.name = '털미네이터';

• 5. tblStaff, tblProject. 서울시에 사는 직원을 제외한 나머지 직원들의 이름, 월급, 담당프로젝트명을 가져오시오.

select * from tblStaff;
select * from tblProject;

select
    s.name,
    s.salary,
    p.project
from tblStaff s
    inner join tblProject p
        on s.seq = p.Staff_seq
            where s.address <> '서울시';

• 6. tblCustomer, tblSales. 상품을 2개(단일상품) 이상 구매한 회원의 연락처, 이름, 구매상품명, 수량을 가져오시오.

select * from tblCustomer;
select * from tblSales;

select
    c.name,
    c.tel,
    s.item,
    s.qty
from tblCustomer c
    inner join tblSales s 
        on c.seq = s.cseq
            where s.qty >= 2;

• 7. tblVideo, tblRent, tblGenre. 모든 비디오 제목, 보유수량, 대여가격을 가져오시오.

select * from tblVideo;
select * from tblRent;
select * from tblGenre;

select 
    v.name, v.qty, g.price
from tblVideo v
    inner join tblRent r
     on v.seq = r.video
        inner join tblGenre g
            on v.genre = g.seq;

• 8. tblVideo, tblRent, tblMember, tblGenre. 2022년 2월에 대여된 구매내역을 가져오시오. 회원명, 비디오명, 언제, 대여가격

select * from tblVideo;
select * from tblRent;
select * from tblGenre;
select * from tblMember;

select 
    m.name, v.name, r.rentdate, g.price
from tblVideo v
    inner join tblRent r
     on v.seq = r.video
        inner join tblGenre g
            on v.genre = g.seq
                inner join tblMember m
                    on m.seq = r.member
                    where rentdate like '22/02/%';

• 9. tblVideo, tblRent, tblMember. 현재 반납을 안한 회원명과 비디오명, 대여날짜를 가져오시오.

select * from tblVideo;
select * from tblRent;
select * from tblMember;

select 
    m.name, v.name, r.rentdate
from tblVideo v
    inner join tblRent r
     on v.seq = r.video
        inner join tblMember m
            on m.seq = r.member
                where Retdate is null;

• 10. employees, departments. 사원들의 이름, 부서번호, 부서명을 가져오시오.

select * from employees;
select * from departments;

select 
    e.First_name || ' ' || e.last_name,
    e.department_id,
    d.department_name
from employees e
    inner join departments d
        on e.department_id = d.department_id;

• 11. employees, jobs. 사원들의 정보와 직업명을 가져오시오.

select * from employees;
select * from jobs;

select
    e.First_name || ' ' || e.last_name,
    j.job_title
from employees e
    inner join jobs j
        on e.JoB_id = j.job_id;

• 12. employees, jobs. 직무(job_id)별 최고급여(max_salary) 받는 사원 정보를 가져오시오.

select * from employees;
select * from jobs;

select 
    *
from employees
    where salary in ( select
                            max(salary)
                        from employees e
                            inner join jobs j
                                on e.JoB_id = j.job_id
                                    group by j.job_id);
                                        
select
    max(salary)
from employees e
    inner join jobs j
        on e.JoB_id = j.job_id
            group by j.job_id;

• 13. departments, locations. 모든 부서와 각 부서가 위치하고 있는 도시의 이름을 가져오시오.

select * from departments;
select * from locations;

select 
    d.department_name, l.city
from departments d
    inner join locations l
        on d.location_id = l.location_id;

• 14. locations, countries. location_id 가 2900인 도시가 속한 국가 이름을 가져오시오.

select * from locations;
select * from Countries;

select 
    c.country_name
from locations l
    inner join countries c
        on l.country_id = c.country_id
            where l.location_id = 2900;

• 15. employees. 급여를 12000 이상 받는 사원과 같은 부서에서 근무하는 사원들의 이름, 급여, 부서번호를 가져오시오.

select * from employees;
select * from departments;

select
    e.First_name || ' ' || e.last_name, e.salary, e.department_id 
from employees e
    inner join departments d
        on e.department_id = d.department_id
            where e.department_id in ( select
                                                d.department_id--e.department_id
                                            from employees e
                                                inner join departments d
                                                    on e.department_id = d.department_id
                                                        where e.salary >= 12000
                                                            group by d.department_id);
    
select
    d.department_id--e.department_id
from employees e
    inner join departments d
        on e.department_id = d.department_id
            where e.salary >= 12000
                group by d.department_id;

• 16. employees, departments. locations.  'Seattle'에서(LOC) 근무하는 사원의 이름, job id, 부서번호, 부서이름을 가져오시오.

select * from employees;
select * from departments;
select * from locations;

select 
    e.First_name || ' ' || e.last_name, e.job_id, e.department_id, d.department_name
from employees e
    inner join departments d
        on e.department_id = d.department_id
            inner join locations l
                on d.location_id = l.location_id
                    where l.city = 'Seattle';

• 17. employees, departments. first_name이 'Jonathon'인 직원과 같은 부서에 근무하는 직원들 정보를 가져오시오.

select * from employees;
select * from departments;

select 
    *
from employees
    where department_id = (select
                                        d.department_id
                                    from employees e
                                        inner join departments d
                                            on e.department_id = d.department_id
                                                where e.first_name = 'Jonathon' );
select
    d.department_id
from employees e
    inner join departments d
        on e.department_id = d.department_id
            where e.first_name = 'Jonathon';

• 18.employees, departments. 사원이름과 그 사원이 속한 부서의 부서명, 그리고 월급을 출력하는데 월급이 3000이상인 사원을 가져오시오.

select
     e.First_name || ' ' || e.last_name, d.department_name, e.salary
from employees e
    inner join departments d
        on e.department_id = d.department_id
            where e.salary >= 3000;

• 19. employees, departments. 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름, 월급을 가져오시오.

select
     d.department_id, d.department_name, e.First_name || ' ' || e.last_name, e.salary
from employees e
    inner join departments d
        on e.department_id = d.department_id
            where d.department_id = 10;

• 20. departments, job_history. 퇴사한 사원의 입사일, 퇴사일, 근무했던 부서 이름을 가져오시오.

select * from departments;
select * from job_history;

select
    j.start_date, j.end_date, d.department_name
from departments d
    inner join job_history j
        on d.department_id = j.department_id;

• 21. employees. 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의 사원번호와 사원이름을 출력하되 각각의 컬럼명을 '사원번호', '사원이름', '관리자번호', '관리자이름'으로 하여 가져오시오.

select * from employees;

select
    e1.employee_id as "사원번호",
    e1.first_name || e1.last_name as "사원이름",
    e2.manager_id as "관리자번호",
    e2.first_name || e2.last_name as "관리자이름"
from employees e1
    inner join employees e2
        on e1.employee_id = e2.manager_id;

• 22. employees, jobs. 직책(Job Title)이 Sales Manager인 사원들의 입사년도와 입사년도(hire_date)별 평균 급여를 가져오시오. 년도를 기준으로 오름차순 정렬.

select * from employees;
select * from jobs;

select 
    e.hire_date as "입사년도",
    avg(salary) as "입사년도별 평균급여"
from employees e
    inner join  jobs j
        on e.job_id = j.job_id
            where job_title = 'Sales Manager'
                group by e.hire_Date
                    order by e.hire_date asc;

• 23. employees, departments. locations. 각 도시(city)에 있는 모든 부서 사원들의 평균급여가 가장 낮은 도시부터 도시명(city)과 평균연봉, 해당 도시의 사원수를 가져오시오. 단, 도시에 근 무하는 사원이 10명 이상인 곳은 제외하고 가져오시오.

select * from employees;
select * from departments;
select * from locations;

select
    l.city as "도시명",
    max(salary) as "평균연봉",
    count(*) as "사원수"
from employees e
    inner join departments d
        on e.department_id = d.department_id
            inner join locations l
                on d.location_id = l.location_id
                    group by city
                        having count(*) > 10
                            order by max(salary) asc;

• 24. employees, jobs, job_history. ‘Public  Accountant’의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 가져오시오. 현재 ‘Public Accountant’의 직책(job_title)으로 근무하는 사원은 고려 하지 말것.

select * from employees;
select * from jobs;
select * from job_history;

select
    job_id
from jobs
    where job_title = 'Public Accountant';
    
select
    *
from employees e
    inner join jobs j
        on e.job_id = j.job_id
            inner join job_history j2
                on e.employee_id = j2.employee_id
                    where j2.job_id = ( select
                                                job_id
                                            from jobs
                                                where job_title = 'Public Accountant');

• 25. employees, departments, locations. 커미션을 받는 모든 사람들의 first_name, last_name, 부서명, 지역 id, 도시명을 가져오시오.

select * from employees;
select * from departments;
select * from locations;

select
    e.first_name,
    e.last_name,
    d.department_name,
    l.location_id,
    l.city
from employees e
    inner join departments d
        on e.department_id = d.department_id
            inner join locations l
                on d.location_id = l.location_id
                    where e.commission_PCT is not null;

• 26. employees. 자신의 매니저보다 먼저 고용된 사원들의 first_name, last_name, 고용일을 가져오시오.

select * from employees;

select
    e1.first_name,
    e1.last_name,
    e2.first_name,
    e2.last_name,
    e2.hire_date
from employees e1
    inner join employees e2
        on e1.employee_id = e2.manager_id
            where e2.hire_Date < e1.hire_date;