Or07Join

/********

# 파일명 : Or07Join.sql

# 테이블 조인

# 설명 : 두 개 이상의 테이블을 동시에 참조하여 데이터를 가져와야할 때 사용하는 쿼리문

********/

/*

[1] inner join(내부조인)

    - 두 개 이상의 테이블로부터 자료를 검색하기 위해서 join을 사용

    일반적으로 primary key(기본키)와 foreign key(외래키,참조키)를 사용하여 join하는 경우가 대부분

    

    - 가장 많이 사용되는 조인문으로 테이블간에 연결조건을 모두 만족하는 행을 검색하는데 사용

    

    - 검색시 검색되는 컬럼이 조인하는 테이블 모드에 존재한다면 

    반드시 컬럼명에 테이블 이름을 다음의 형태로 기술해야한다. "테이블명.컬럼명"

    

    - inner join은 자식테이블(foreign key가 있는 테이블)을 기준으로 모든 데이터를 가져온다.

    예를들면 자식테이블의 전체 레코드가 10개면 inner join한 결과의 레코드 수는 10개이다.

    

사용구문

[2개의 테이블을 조인할때]

방법1(표준 SQL방식)

    select 

        컬럼1, 컬럼2, ... , 컬럼N

    from 

        테이블1 inner join 테이블2

    on 

        테이블1.기본키컬럼 = 테이블2.기본키컬럼

    where 조건1, 조건2

    

방법2(Oracle 방식)

    select 

        컬럼1, 컬럼2, .... , 컬럼N

    from 

        테이블1, 테이블2

    where

        테이블1.기본키컬럼 = 테이블2.외래키컬럼 and 조건1, 조건2     

*/

-- employees, departments 테이블의 데이터 확인

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID

from employees;

select * from DEPARTMENTS;

/*

employees테이블과 departments테이블을 조인하여 각 직원이 어떤 부서에서

근무하는지를 출력하시오.

출력결과] 사원아이디, 이름1, 이름2, 이메일, 부서번호, 부서명

*/

-- SQL 표준방식(별칭 사용)

select 

    employees.employee_id, first_name, last_name, email, 

    departments.department_id, department_name

from 

    employees inner join departments

on 

    employees.department_id = departments.department_id;

-- SQL 표준방식(별칭 사용)

select 

    emp.employee_id, emp.first_name, emp.last_name, emp.email, 

    dep .department_id, dep.department_name

from 

    employees emp inner join departments dep

on 

    emp.department_id = dep.department_id;

    

/*

[3개 이상의 테이블을 조인할때]

표준 SQL 방식

    select

        컬럼1, 컬럼2, .... , 컬럼N

    from

        테이블1 inner join 테이블2

            on 테이블1.기본키컬럼 = 테이블2.외래키컬럼

        테이블2 inner join 테이블3

            on 테이블2.기본기컬럼 = 테이블3.외래키컬럼

            

Oracle 방식

    select

        컬럼1, 컬럼2, .... , 컬럼N

    from

        테이블1, 테이블2, 테이블3

    where

        테이블1.기본키컬럼 = 테이블2.외래키컬럼

        and

        테이블2.기본기컬럼 = 테이블3.외래키컬럼

*/

/*

3개의 테이블 조인

    : seattle(시에틀)에 위치한 부서에서 근무하는 

    직원의 정보를 출력하는 쿼리문을 작성하시오.

    출력정보] 사원이름, 이메일, 부서ID, 부서명, 담당업무ID, 담당업무명, 근무지역

*/

select 

    emp.first_name, emp.last_name, emp.email, emp.department_id, emp.job_id,

    dep.department_name,

    job.job_title,

    loc.city

from 

    employees emp inner join departments dep

        on emp.department_id = dep.department_id

    inner join locations loc

        on dep.location_id = loc.location_id

    inner join jobs job

        on emp.job_id = job.job_id

where

    loc.city = 'Seattle';

-- Oracle 방식

select

    e.first_name, e.last_name, e.email, e.department_id, e.job_id,

    d.department_name,

    j.job_title,

    l.city

from

    employees e, departments d, jobs j, locations l

where

    l.city = 'Seattle'

    and

    e.department_id = d. department_id

    and

    e.job_id = j.job_id

    and

    d.location_id = l.location_id;

/*

[2] outer join(외부조인)

outer join은 inner join과는 달리 두 테이블에 조인조건이 정확히 일치하지 않더라도

기준이 되는 테이블에서 결과값을 가져오는 join문이다.

outer join문을 사용할때는 반드시 outer전에 데이터를 

어느쪽 테이블에서 가져올지 기술해야한다.

left(왼쪽테이블), right(오른쪽테이블), full(양쪽테이블)

[참고] inner join에 비해 outer join의 쿼리처리속도가 느리다.

그래서 join문을 사용해야 한다면 inner join을 사용하는것이 웹어플리케이션의

성능향상에 도움이 된다.

- left outer join

    : 조인문의 왼쪽을 중심으로 왼쪽 테이블의 데이터를 모두 가져온다.

- right outer join

    : 조인문의 오른쪽을 중심으로 오른쪽 테이블의 데이터를 모두 가져온다.

- full outer join

    : 조인문 양쪽에 있는 모든 테이블에서 데이터를 모두 가져온다.

사용법 :

    select

        컬럼1, 컬럼2, .... , 컬럼N

    from

        테이블1 left[right, full] outer join 테이블2

        on 테이블1.컬럼명 = 테이블2.컬럼명

    where

        조건1, 조건2, .... , 조건N

*/

-- SQL 표준방식

/* 전체직원의 사원번호, 이름, 부서아이디, 부서명, 지역을 출력하시오. */

select

    emp.employee_id, emp.first_name, emp.last_name, emp.department_id,

    dep.department_name,

    loc.city

from

    employees emp left outer join departments dep

        on emp.department_id = dep.department_id

    left outer join locations loc

        on dep.location_id = loc.location_id

where

    1 = 1;

/*

Oracle 방식

    : outer join 연산자인(+)룰 where절에 붙여준다

*/

/* 전체직원의 사원번호, 이름, 부서아이디, 부서명, 지역을 출력하시오. */

select

    e.employee_id, e.first_name, e.last_name, 

    d.department_id, d.department_name,

    l.city, l.state_province

from

    employees e, departments d, locations l

    

where

    1 = 1 and e.department_id = d.department_id (+)

    and

    d.location_id = l.location_id (+);

/*

사원테이블에서 각 사원의 매니저 아이디와 매니저 이름을 출력하시오

여기서 매니저는 직속상관 혹은 상사를 뜻한다.

*/

select

    empCrew.employee_id, empCrew.first_name, empCrew.last_name,

    empMgr.employee_id "매니저ID", empMgr.first_name "매니저이름1",

    empMgr.last_name "매니저이름2"

from

    employees empCrew, employees empMgr

where

    empCrew.manager_id = empMgr.employee_id

order by 

    empCrew.employee_id;

/*

1. 하나의 테이블이 각각 사원정보테이블, 매니저정보테이블로 나눠진다.

2. 사원의 매니저 아이디와 매니저의 사원 아이디를 매칭

3. 각각의 테이블 별칭을 이용해서 필요한 정보를 출력한다.

*/

/*

using 사용하기 : join문에서 주로 사용하는 on절을 using으로 대체가능

*/

/* 전체직원의 사원번호, 이름, 부서아이디, 부서명, 지역, 국가명을 출력하시오. */

select

    employee_id, first_name, last_name, 

    department_id, department_name,

    city, state_province,

    country_id, country_name

from

    employees e left outer join departments d

        using(department_id)

    left outer join locations l

        using(location_id)

    left outer join countries c

        using(country_id)

where 1=1;

/*

using절에 사용된 식별자 department_id, location_id, contry_id의 경우

select절에서 테이블의 식별자를 붙히면 아래와 같은 쿼리 오류발생됨

ORA-25154: USING 절의 열 부분은 식별자를 가질 수 없음

25154. 00000 -  "column part of USING clause cannot have qualifier"

using에 사용된 식별자는 좌,우측 테이블에 동시에 존재하는 컬럼이라는것을

전제로 작성하기 때문에 굳이 식별자를 붙힐 이유가 없다.

*/

/*

#조인 

Oracle 개인폴더에 “03조인” 문서로 업로드 해주세요.

*/

/*

1. inner join 방식중 오라클방식을 사용하여 first_name 이 Janette 인 

사원의 부서ID와 부서명을 출력하시오.

출력목록] 부서ID, 부서명

*/

select

    e.first_name, d.department_id,d.department_name

from

    employees e, departments d

where

    e.department_id = d.department_id and

    e.first_name = 'Janette';

/*

2. inner join 방식중 SQL표준 방식을 사용하여 사원이름과 함께 그 사원이 

소속된 부서명과 도시명을 출력하시오

출력목록] 사원이름, 부서명, 도시명

*/

select

    e.first_name, e.last_name, d.department_name, 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

    1=1

;

/*

3. inner join과 using 연산자를 사용하여 50번 부서(DEPARTMENT_ID)에 

속하는 모든 담당업무(JOB_ID)의 고유목록을 부서의 도시명(CITY)을 포함하여 출력하시오.

출력목록] 담당업무ID, 부서ID, 부서명, 도시명

*/

select

    distinct e.job_id, department_id, d.department_name, l.city

from

    employees e

        inner join departments d

            using(department_id)

        inner join locations l

            using(location_id)

where

    department_id=50;

/*

[주의] using절에 사용된 컬럼명은 join한 양쪽테이블에 동일하게 존재하는 컬럼을

기반으로 하기 때문에 select절이나 where절에 사용할 때는 테이블명 혹은

별칭을 사용하면 쿼리에러가 발생된다.

*/

/*

4. 내부조인을 사용하여 커미션(COMMISSION_PCT)을 받는 모든 

사원의 이름, 부서명, 도시명을 출력하시오. 

출력목록] 사원이름, 부서ID, 부서명, 도시명

*/

select

    emp.first_name, emp.last_name, dep.department_id, dep.department_name, loc.city

from

    employees emp, departments dep, locations loc

where

    emp.department_id = dep.department_id and

    dep.location_id = loc.location_id and

    commission_pct is not null

;

/*

5. 사원의 이름(FIRST_NAME)에 'A'가 포함된 모든사원의 이름과 부서명을 출력하시오.

출력목록] 사원이름, 부서명

*/

select

    first_name, last_name, department_name

from

    employees emp inner join departments dep on emp.department_id=dep.department_id

where   first_name like '%A%';

/*

6. “city : Toronto / state_province : Ontario” 에서 근무하는 모든 사원의 이름, 업무명, 부서번호 및 부서명을 출력하시오.

출력목록] 사원이름, 업무명, 부서ID, 부서명

*/

select

    e.first_name, e.last_name, j.job_title, d.department_id, d.department_name

from employees e, departments d, locations l, jobs j

where

    city = 'Toronto' and state_province='Ontario' and

    e.department_id = d.department_id and

    d.location_id = l.location_id and

    e.job_id = j.job_id

;

/*

7. self join을 사용하여 사원의 이름 및 사원번호를 

관리자 이름 및 메니져 번호와 함께 출력하시오.

즉 관리자가 있는 사원만 출력해야 한다.

출력목록] 사원이름, 사원ID, 메니져이름, 메니져아이디

*/

--사원과 매니져 레코드를 확인하기 위한 쿼리문

select employee_id, first_name, last_name, manager_id from employees;

select

    Crew.first_name, Crew.employee_id,

    Manager.first_name, Manager.employee_id, Crew.manager_id

from

    employees Manager , employees Crew

where

    Manager.employee_id = Crew.manager_id

order by Crew.employee_id desc

;

/*

8. outer join, self join을 사용하여 관리자가 없는 사원을 포함하여 

사원번호를 기준으로 내림차순 정렬하여 출력하시오.

출력목록] 사원이름, 사원ID, 메니져이름, 메니져아이디

*/

select

    Crew.employee_id, Crew.first_name,Crew.last_name,

    Mgr.employee_id, Mgr.first_name, Mgr.last_name

from

    employees Mgr, employees Crew

where

   Crew.manager_id = Mgr.employee_id  (+)

order by

    Crew.employee_id desc;

 

/*

9. self join을 사용하여 “Timothy / Gates” 사원의 이름, 부서번호 

그리고 해당 사원과 동일한 부서에서 근무하는 사원의 이름을 출력하시오. 

출력목록] Timothy사원이름과 성, 부서ID, 사원이름과 성

*/

-- 이름이 Timothy 인 사원의 정보출력

select * from employees where first_name ='Timothy' and last_name = 'Gates';

select

    Timothy.first_name, Timothy.last_name, Timothy.department_id,

    Crew.first_name, Crew.last_name

from

    employees Timothy , employees Crew

where

    Timothy.department_id = Crew.department_id and

    Timothy.first_name = 'Timothy' and

    Crew.first_name != 'Timothy'

;

/*

Timothy의 동료가 Timothy일 수는 없으므로 사원의 이름이 Timothy인

레코드 하나를 제외시켜야 한다.

*/

/*

10. self join을 사용하여 “Kimberely / Grant” 사원보다 늦게 입사한 

사원의 이름과 입사일을 출력하시오.

출력목록] 사원이름, 입사일

*/

select * from employees where first_name='Kimberely';

select

    Crew.first_name, Crew.last_name, Crew.hire_date

from

    employees Kimberely, employees Crew

where

    Kimberely.first_name='Kimberely' and

    Kimberely.hire_date < Crew.hire_date

order by Crew.hire_date asc

;

/*

11. self join을 사용하여 메니져보다 먼저 입사한 사원의 정보를 출력하시오. 

사원의 이름과 입사일, 메니져의 이름과 입사일을 출력하도록 한다. 

출력목록] 사원이름, 사원입사일, 메니져이름, 메니져입사일 

*/

select

    crew.first_name, crew.last_name, crew.hire_date 사원입사일,

    mgr.first_name, mgr.last_name, mgr.hire_date 매니저입사일

from

    employees mgr, employees crew

where

    mgr.employee_id = crew.manager_id

    

    and

    mgr.hire_date < crew.hire_date

;