[Oracle] Join(조인)

조인(join)

 

EMPLOYEES               DEPARTMENTS                     JOIN

------------------------------------------------------------------------------------------

사             부                 부              부                사        부        부

원             서        +       서              서       =       원        서        서

이             번                 번              이                이        번        이

름             호                 호              름                름        호        름

 

※ 종류

1. Inner join : 같은 것끼리만 연결

2. Outer join : 한쪽을 기준(모두포함)해서 연결

                       left join : 왼쪽 컬럼 모두포함

                       right join : 오른쪽 컬럼 모두포함

3. full join : 왼쪽, 오른쪽 모두 포함

4. self join : 자기 자신 테이블과 연결

5. cross join : 모든 경우의 수로 연결

6. non equijoin : 범위에 속하는지 여부를 확인

7. n개 테이블 조인 : 여러 개의 테이블 조인

 

※ 방법

1. 오라클 구문 전용

2. Ansi 표준 구문

 

 

Inner Join

ex5) inner join : 같은 것끼리만 조인

사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오

 

방법1(오라클 전용 구문)

select employee_id,

          employees.department_id,

          department_name

from employees, departments

where employees.department_id = departments.department_id;

 

방법2(오라클 전용 구문)

select employee_id,

          e.department_id,

          department_name

from employees e, departments d

where e.department_id = d.department_id;

 

방법3(Ansi 표준)

select employee_id, department_id, department_name

from employees join departments using(department_id);

 

---

 

[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여 부서가 위치한 도시를 알아내시오

 

department_id         city

---------------------------------------

10                         Seattle

 

---

 

Outer Join

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 107레코드

 

방법1(오라클 전용 구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+);

 

방법2(Ansi 표준)

select last_name, department_id, department_name

from employees

left join departments using(department_id);

 

ex7) outer join(right) : 오른쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122 레코드

 

방법1(오라클 전용 구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id(+) = d.department_id;

 

방법2(Ansi 표준)

select last_name, department_id, department_name

from employees

right join departments using(department_id);

 

Full Join

ex8) full join : 왼쪽, 오른쪽 테이블을 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드

 

--방법1(오라클 전용 구문) : 없다

--방법2(Ansi표준)

select last_name, department_id, department_name

from employees

full join departments using(department_id);

 

ex9) inner join : 두 개의 컬럼이 일치 하는 경우

        부서ID와 매니저ID가 같은 사원을 연결 하시오 → 32 레코드

        (관련 테이블 : departments, employees)

             last_name        department_id         manager_id

 

방법1(오라클 전용 구문)

select e.last_name, d.department_id, d.manager_id

from employees e, departments d

where e.department_id=d.department_id and e.manager_id=d.manager_id;

 

방법2(Ansi표준)

select last_name, department_id, manager_id

from employees

inner join departments using(department_id, manager_id);

 

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

            departments(location_id), locations2(loc_id)

 

테이블 복사

create table locations2 as select * from locations;

select * from locations;

select * from locations2;

alter table locations2 rename column location_id to loc_id;

select * from locations2; -- 컬럼명이 바뀌었다

 

방법1(오라클 전용 구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id = l.loc_id;

 

방법2(Ansi 표준)

select department_id, city

from departments

join locations2 on(location_id = loc_id);

→ 다른 테이블끼리 join시에는 on을 사용해야 한다. (using x)

 

방법3(Ansi 표준)

select department_id, city

from departments d

join locations2 l on(d.location_id = l.loc_id);

 

Self Join

ex11) self 조인 : 자기 자신의 테이블과 조인하는 경우 사원과 관리자를 연결하시오

 

사원번호        사원이름         관리자

--------------------------------------------------

    101              Kochhar            King

 

        EMPLOYEES                                             EMPLOYEES

--------------------------------------------------------------------------------------------

employee_id, last_name(사원이름)                last_name(관리자)

조건    employee_id = manager_id

 

select employee_id, manager_id, last_name from employees; → e

select employee_id, last_name from employees; → m

 

 

방법1

select e.employee_id as 사원번호,

          e.last_name as 사원이름,

          m.last_name as 관리자

from employees e, employees m

where m.employee_id = e.manager_id;

 

방법2

select e.employee_id as 사원번호,

          e.last_name as 사원이름,

          m.last_name as 관리자

from employees e

join employees m on(m.employee_id = e.manager_id);

 

Cross Join

ex12) cross join : 모든 행에 대해 가능한 모든 조합을 생성하는 조인

select * from countries, locations; → 575레코드

 

select * from countries cross join locations;

 

Non Equi Join

상호평가 등급, 성적 등급과 같이 특정 범위와 등급을 지정하고 
해당 범위에 들었을 때 등급으로 데이터를 표현하기 위해 사용되는 JOIN

 

NON-EQUI JOIN은 EQUI JOIN과 다르게 특정 칼럼을 범위로 JOIN 시킨다.

같냐 아니냐가 아닌 범위를 기준으로 따지기에 Non Equi Join이라 불리는 것이지
같지 않은 범위를 추출하니까  Non Equi Join인 것이 아니기에 개념에 유의해야 한다.

 

ex13) Non Equijoin (넌 이큐조인)

컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때

 

on ( 컬럼명 between 컬럼명1 and 컬럼명2)     or

on ( 컬럼명 >= 컬럼명1 and 컬럼명 <= 컬럼명2)

 

create table salgrade(

salvel varchar2(2),

lowst number,

highst number);

 

insert into salgrade values('A', 20000, 29999);

insert into salgrade values('B', 10000, 19999);

insert into salgrade values('C', 0, 9999);

commit;

 

select * from salgrade;

 

select last_name, salary, salvel

from employees

join salgrade on(salary between lowst and highst)

order by salary desc;

 

n개 테이블 조인

ex14) n(여러)개의 테이블은 조인

업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오

(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)

 

[분석]

EMPLOYEES             JOBS               DEPARTMENTS

-------------------------------------------------------------------------------

department_id            job_id               department_id

job_id

 

select last_name, job_title, department_name

from employees

join departments using(department_id)

join jobs using(job_id);

 

---

 

[문제3] 위치ID, 부서ID을 연결해서 사원이름, 도시, 부서이름을 출력하시오

            (관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)

조건1 : 사원이름, 도시, 부서이름으로 제목을 표시하시오

조건2 : Seattle 또는 Oxford 에서 근무하는 사원

조건3 : 도시 순으로 오름차순 정렬하시오

 

사원이름       도 시         부서이름
-----------------------------------------------

Hall              Oxford          Sales

 

---

 

[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오

            (관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)

 

조건1 : 사원번호, 사원이름, 부서이름, 도시, 도시주소, 나라이름으로 제목을 표시하시오

조건2 : 도시주소에 Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오

조건3 : 나라이름, 도시별로 오름차순 정렬하시오

조건4 : 모든 사원을 포함한다.

---

 

 

 

 

 

문제답 : 

--문제2~4번에 해당하는 테이블의 컬럼정보
select department_id, department_name, manager_id, location_id from DEPARTMENTS; 
select location_id, street_address, postal_code, city, state_province, 
        country_id from LOCATIONS;
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, 
        salary, commission_pct, manager_id, department_id from EMPLOYEES;
select country_id, country_name, region_id from COUNTRIES;   
--문제2 (4가지 방법)
select department_id, city from DEPARTMENTS, LOCATIONS where DEPARTMENTS.location_id = LOCATIONS.location_id;
select d.department_id, l.city from DEPARTMENTS d, LOCATIONS l where d.location_id = l.location_id;
select department_id, city from DEPARTMENTS join LOCATIONS using(location_id);
select d.department_id, l.city from DEPARTMENTS d join LOCATIONS l on d.location_id = l.location_id;

--문제3
select (e.first_name||' '||e.last_name) as 사원이름 , l.city as 도시, d.department_name as 부서이름
from DEPARTMENTS d
join LOCATIONS l using(location_id)
join EMPLOYEES e  using(department_id)
where l.city ='Seattle' or l.city ='Oxford'
order by l.city asc;  -- or 2 asc;

--문제4 (모든 사원을 포함한다는 left join. but, 어차피 where조건에 걸려서 의미가 없다.)
select e.employee_id as 사원번호, (e.first_name||' '||e.last_name) as 사원이름, d.department_name as 부서이름, 
    l.city as 도시, l.street_address as 도시주소, c.country_name as 나라이름
from DEPARTMENTS d
join LOCATIONS l using(location_id)
join EMPLOYEES e using(department_id)
join COUNTRIES c using(country_id)
where l.street_address like '%Ch%' or
      l.street_address like '%Sh%' or
      l.street_address like '%Rd%'
order by c.country_name asc, l.city asc;  -- or order by 6, 4;

 

 

 

---

 

참고) 대량의 쿼리를 insert할 때 하나씩 수행하면 DB가 뻗어버린다.
(insert 한 번 할 때마다 컬럼을 하나씩 다 조회하기때문에)

1. 한글 버전으로 맞춰서 한글폰 깨지지 않도록 설정 :
   도구 → 환경설정 → 환경 → 인코딩 UTF-8 로 변경
2. @@D:\\bitcamp\\Oracle\\stExe_학생.sql; 수행 → 자동 commit됨
3. select * from emp; 으로 테이블 insert 확인

문제1) 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색하시오
(GROUP, HAVING)
평균을 소수이하 2째 자리
테이블 : STUDENT

 

문제2) 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색하시오

 

조인 문제

문제1) 송강 교수가 강의하는 과목을 검색하시오.
테이블 : PROFESSOR P, COURSE C
컬럼 : PNO, PNAME, CNO, CNAME

 

문제2) 화학 관련 과목을 강의하는 교수의 명단을 검색하시오
테이블 : PROFESSOR P, COURSE C
컬럼 : PNO, PNAME, CNO, CNAME

 

문제3) 화학과 1학년 학생의 기말고사 성적을 검색하시오
테이블 : STUDENT ST, SCORE SC, COURSE CO
컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT

 

문제4) 화학과 1학년 학생의 일반화학 기말고사 점수를 검색하시오
테이블 : STUDENT ST, SCORE SC, COURSE CO
컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT

 

문제 제출답 : 

select * from emp;

select * from student;
--문제1
select major as 학과, to_char(avg(avr), 9.99) as 평점평균 from student 
where major not in('화학')
group by major;

--문제2
select major as 학과, to_char(avg(avr), 9.99) as 평균평점 from student 
where major not in('화학')
group by major having avg(avr)>=2.0;

--조인문제
select pno, pname, section, orders, hiredate from professor p; 
select cno, cname, st_num, pno from course co; 
select sno, sname, sex, syear, major, avr from student st; 
select sno, cno, result from score sc; 

--문제1
select c.cname from course c join professor p on p.pno = c.pno where p.pname = '송강';

--문제2
select p.pname from professor p join course c on p.pno = c.pno where c.cname like '%화학%';

--문제3
select st.sname as 이름, sc.result as "기말고사 점수" from score sc 
join student st using(sno)
join course co using(cno)
where st.major = '화학' and st.syear = '1' ;

--문제4
select st.sname as 이름, sc.result as "기말고사 점수" from score sc 
join student st using(sno)
join course co using(cno)
where st.major = '화학' and st.syear = '1' and co.cname = '일반화학';






-- 정답
--문제1) 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색하시오 (GROUP, HAVING)
--       평균을 소수이하 2째 자리에서 반올림
--       테이블 : STUDENT
select major, round(avg(avr), 2) from student where major != '화학' group by major;
select major, round(avg(avr), 2) from student group by major having major != '화학';

--문제2) 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 미만인 정보를 검색하시오 
-- (원래 이상인데 미만으로 문제 바뀜)
select major, round(avg(avr), 2) 
from student
where major != '화학'
group by major
having round(avg(avr), 2) < 2;

--조인 문제
--문제1) 송강 교수가 강의하는 과목을 검색하시오.
--테이블 : PROFESSOR P, COURSE C
--컬럼 : PNO, PNAME, CNO, CNAME
select p.pno, pname, cno, cname
from professor p, course c
where p.pno = c.pno and p.pname = '송강';

select pno, pname, cno, cname
from professor
join course c using(pno)
where pname = '송강';

--문제2) 화학 관련 과목을 강의하는 교수의 명단을 검색하시오
--테이블 : PROFESSOR P, COURSE C
--컬럼 : PNO, PNAME, CNO, CNAME
select p.pno, pname, cno, cname
from professor p, course c
where p.pno = c.pno and c.cname like '%화학%';

select pno, pname, cno, cname
from professor
join course using(pno)
where c.cname like '%화학%';

--문제3) 화학과 1학년 학생의 기말고사 성적을 검색하시오
--테이블 : STUDENT ST, SCORE SC, COURSE CO
--컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT
select st.sno, sname, major, syear, sc.cno, cname, result
from student st, score sc, course co
where st.sno = sc.sno and sc.cno = co.cno and major = '화학' and syear = '1';

select sno, sname, major, syear, cno, cname, result
from student
join score using(sno) 
join course using(cno)
where major = '화학' and syear = '1';

--문제4) 화학과 1학년 학생의 일반화학 기말고사 점수를 검색하시오
--테이블 : STUDENT ST, SCORE SC, COURSE CO
--컬럼 : SNO, SNAME, MAJOR, SYEAR, CNO, CNAME, RESULT
select st.sno, sname, major, syear, sc.cno, cname, result
from student st, score sc, course co
where st.sno = sc.sno and sc.cno = co.cno and major = '화학' and cname = '일반화학' and syear = '1';

select sno, sname, major, syear, cno, cname, result
from student 
join score using(sno)
join course using(cno)
where major = '화학' and cname = '일반화학' and syear = '1';