DAY-24. Oracle Join

🔐 2022-04-04 - 2

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

2022-04-04 -2

1️⃣ Join

• 두 개 이상의 테이블을 결합 하는 것 -> 조합하려고 하는 테이블에서 각 테이블의 공통된 컬럼을 데이터로 합쳐서 표현하는 것
• 2중 for문이라고 생각

🚀조인문을 사용할때 각 테이블이 가지고 있는 컬럼명 같을 때/ 컬럼명일 다를 때

1) 컬럼명이 다를 때

🔎
//부서코드가 같은 employee, department 테이블의 사번, 사원명, 부서코드, 부서명 조회

// oracle 문법
// emp_id, emp_name, dept_code는 employee만 가지고 있고, dept_title은 department만 가지고 있다
select emp_id, emp_name, dept_code, dept_title
    from employee, department
        where dept_code = dept_id;

// ANSI 문법
select emp_id, emp_name, dept_code, dept_title
    from employee join department
        on(dept_code = dept_id);

2) 컬럼명이 같을 때

• 각 테이블의 별칭을 정해줘야한다

🔎
// 사번, 사원명, 직급코드, 직급명
// 각 테이블의 별칭을 정해줘야한다

// oracle
select
    emp_id, emp_name, e.job_code, job_name // e.job_code이든 j.job_code 이든 상관 x -> 어차피 같아서
from employee e, job j       // 별칭 정해주기
    where e.job_code = j.job_code;    // 별칭.컬럼명 = 별칭.컬럼명

// ANSI : 두 테이블의 같은 컬럼명 -> using(컬럼명)
select
    emp_id, emp_name, job_code,job_name
from employee join job
    using(job_code);

🚀 Join의 종류

Cross Join (상호 조인)

• 조인되는 테이블에 조건을 걸어주지 않아서 모든 데이터(모든 경우의 수가) 조합되어 나오는 경우
• 한 테이블의 총 행수 * 다른 테이블의 총 행수

🔎
select * from employee, department; // oracle
select * from employee cross join department; // ANSI

(Inner) Join (내부 조인/ 교집합)

가장 많이 용하는 Join 형태 : 테이블 A와 테이블 B에서 조건이 맞는 데이터만 변환

• select 컬럼.. from 테이블 A, 테이블 B where 조건;
• select 컬럼.. from 테이블 A inner join 테이블 B on 조건;
• select 컬럼.. from 테이블 A join 테이블 B on 조건;

🔎
select * from employee, department where dept_code = dept_id; // oracle
select * from employee join department on (dept_code = dept_id); // ANSI

🔎
// 총무부, 회계관리부인 사원들의 사원명, 이메일, 부서명, 부서코드만 조회

// oracle
select
    emp_name "사원명",
    email "이메일",
    dept_title "부서명",
    dept_id "부서코드"
from employee, department
    where dept_code = dept_id and dept_title in ('총무부','회계관리부');

// ANSI
select
    emp_name "사원명",
    email "이메일",
    dept_title "부서명",
    dept_code "부서코드"
from employee inner join department
    on(dept_code = dept_id)
    where dept_title in ('총무부','회계관리부');

🔎
// 직책이 대리인 사람들의 급여 목록 확인
// 대리급 사원들의 사원명, 직급코드, 직급명, 부서코드 (null - 인턴), 월급 조회
// 월급 -> 원화 기호로
// 이름 오름차순, 월급 내림차순 정렬

// oracle
select * from employee;
select * from job;
select
    emp_name "사원명",
    j.job_code "직급코드",
    job_name "직급명",
    nvl(dept_code,'인턴') "부서코드",
    to_char(salary,'L999,999,999') "연봉"
from employee e, job j
    where e.job_code = j.job_code and job_name = '대리'
    order by 1 asc, 4 desc;

// ANSI
select
    emp_name "사원명",
    job_code "직급코드",
    job_name "직급명",
    nvl(dept_code,'인턴') "부서코드",
    to_char(salary,'L999,999,999') "월급"
from employee inner join job
    using(job_code)
    where job_name = '대리'
    order by 1,5 desc;

Left Outer Join (외부 조인/ 합집합 → 왼쪽 외부 조인)

조인하는 테이블 A의 테이터를 모두 반환, 테이블 B는 조건 구문에 일치하는 데이터만 반환

→ inner join 조건을 걸게 되면 조건에 맞는 데이터만 반환

→ left outer join 조건이 일치하지 않아도 왼쪽 테이블의 데이터는 모두 반환

❗ inner join을 사용할 경우 조건을 일치하지 않는 데이터가 full로 조회되지 않은 경우 발생 → left outer join 사용하면 왼쪽으로 둔 테이블은 전체가 나오고 오른쪽으로 둔 테이블은 조건에 맞는 데이터만 출력

방법)

oracle : select 컬럼 .. from 테이블 A, 테이블 B where 컬럼 = 컬럼(+);
ANSI : select 컬럼..  from 테이블 A left outer join 테이블 B on() / using()

🔎
// oracle
select emp_name,dept_title
from employee, department
    where dept_code = dept_id(+);

// ANSI
select emp_name, dept_title
from employee left outer join department
    on(dept_code = dept_id);

🔎
// 기술지원부를 제외하고 모든 부서 직원의 사번, 사원명, 부서명, 연봉 조회
select emp_id "사번",
       emp_name "사원명",
       nvl(dept_title,'미정')"부서명",
       to_char(salary * 12,'L999,999,999') "연봉"
from employee left outer join department on (dept_code = dept_id)
    where dept_title != '기술지원부' or dept_code is null
    order by 부서명,사번,연봉;

• != / = / like / not like 같은 equal 연산자를 이용하게 되면 null 값에 대한 제대로 된 비교가 되지 않고 모두 제외
• null 값도 포함시키고 싶다면 우회적으로 추가 조건을 붙여줌 -> dept_code is null

Right Outer Join (합집합 / 오른쪽 외부 조인)

조인하고자 하는 테이블 A와 테이블 B가 있을 때, 테이블 A는 조건 구문에 일치하는 데이텀나 반환, 테이블 B는 조건에 상관없이 모든 데이터 변환

방법)

oracle : select 컬럼 .. from 테이블 A, 테이블 B where 컬럼(+) = 컬럼;
ANSI : select 컬럼..  from 테이블 A right outer join 테이블 B on() / using()

• Left outer Join 과 다를게 없다

Full Outer Join(합집합)

테이블 A와 테이블 B를 조인할 때 조건에 맞지 않더라도 모든 데이터 출력

🔎
select emp_name, dept_title
from employee full outer join department
    on(dept_code = dept_id);

• null 값들도 다 출력

Non-Equi Join(비등가 조인)

지정한 컬럼의 값이 일치하는 경우가 아니라, 값의 범위에 포함되는 행을 연결(between, <, >, ≤ 등)

Self Join

다른 테이블이 아닌 같은 테이블을 조인하는 것

• 똑같은 테이블을 join 하기 때문에 각 테이블에 별칭을 반드시 지정
• 사용하려는 컬럼명이 어떤 별칭에 해당하는 컬럼명을 사용하려는건지 명확히 해줘야한다

🔎
//manager_id에 해당하는 사원명(매니저명)도 추가하고 싶다면?
// 담당 매니저의 사번에 해당하는 이름을 출력 하는 문제
// 윤은해 매니저 사번은 207 -> 직원 사번 -> 207은 하이유(매니저)

select
    e1.emp_id "직원사번",
    e1.emp_name "직원명",
    e1.manager_id "매니저 사번",
    e2.emp_name "매니저명"
from employee e1 join employee e2
    on(e1.manager_id = e2.emp_id);

다중 Join

여러 개의 조인문을 사용하는 경우

• 순서에 유의

🔎
// employee departement 조인 -> 사번, 사원명, 부서코드, 부서명

select emp_id "사번",
       emp_name "사원명",
       dept_code "부서코드",
       dept_title "부서명"
from employee join department
    on(dept_code = dept_id);

-- 사번, 사원명, 부서코드, 부서명 + 부서지역명
select * from location;
select * from department;

select
    dept_id,
    dept_title,
    local_name
from department join location
    on(location_id = local_code);

// ANSI
select
    emp_id "사번",
    emp_name "사원명",
    dept_code "부서코드",
    dept_title "부서명",
    local_name "부서지역명"
from employee join department on(dept_code = dept_id)
    join location on(location_id = local_code);

// oracle
select
    emp_id "사번",
    emp_name "사원명",
    dept_code "부서코드",
    dept_title "부서명",
    local_name "부서지역명"
from employee, department, location
    where dept_code = dept_id
    and location_id = local_code;