Oracle Select 활용및 JOIN

/*
    JOIN
    두개 이상의 테이블을 연결해서 데이터를 검색하는 방법이다.
    보통 두개이상의 행(row) 들의 공통된 값
    Primary key(기본키), Foreign key(외래키)값을 사용해서 조인한다.
    
    Primary key(기본키) : 테이블에서 중복이 되지 않는 키 
    Foreign key(외래키) : 다른 테이블에서 Primary key, Unique key일 가능성이 크다.
  
    조인은 테이블 두개를 합치는개념이 아닌 다른테이블의 데이터를 끄집어 내기 위해 사용한다
    
    별이 다섯개~~!!!
    inner JOIN ***** == 교집합
    cross JOIN X
    outer JOIN
        left   ***
        right  ***
    self JOIN  *****

*/

-- inner JOIN
-- Ansi SQL
-- 어느쪽의 테이블이 명시되어있는지 구분하할때만 무조건 .을 통해 붙인다
SELECT employee_id, first_name, e.department_id, d.department_id, d.department_name
FROM employees e INNER JOIN departments d
    --e.department_id 와 d.department_id가 같을때
    ON e.department_id = d.department_id;
    
-- Oracle

SELECT  employee_id, first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Shanta 의 업무명
SELECT e.employee_id, e.first_name,
    e.job_id, j.job_id,
    j.job_title
FROM employees e, jobs j
where e.job_id = j.job_id
    AND e.first_name = 'Shanta';

-- 147번 사원의 부서명, 업무명을 구하라.

SELECT e.employee_id, e.first_name, 
    e.department_id, d.department_id,
    e.job_id, j.job_id,
    d.department_id, j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
    AND e.job_id = j.job_id
    AND e.employee_id = 147;


-- CROSS Join
-- ansi
-- 쓸일이 없다
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_id
FROM employees e cross join departments d;

-- oracle
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_id
FROM employees e, departments d;


-- full outer Join
-- ansi
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;
    
    SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id
WHERE e.department_id IS NULL OR d.department_id IS NULL;

-- outer join
-- left(right)
-- ansi

--왼쪽의 전부가 출력된다
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e left outer join departments d
    on e.department_id = d.department_id; 


-- oracle
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- 왼쪽으로 가는 기호 기호가 없는쪽에서 있는쪽으로 간다

--교집합을 뺀 왼쪽만 출력할때
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
    AND e.department_id IS NULL; 

-- self JOIN : 동일한 테이블을 Join
-- 사원의 매니저 즉 상사를 찾기
-- 왼쪽은 사원의 정보를 뽑고 오른쪽은 맞춰보면서 상사의 정보를 꺼낸다.
SELECT a.employee_id, a.first_name, -- 사원
    a.manager_id, b.employee_id,
    b.first_name -- 상사의 이름
FROM employees a, employees b
where a.manager_id = b.employee_id;

-- 계층형 구조 오름, 내림
-- DB개발자들이 사용
SELECT a.employee_id, a.first_name as "사원",
    a.manager_id as "사원의 상사",
    b.employee_id,
    b.first_name as "상사"
FROM employees a, employees b
WHERE a.manager_id = b.employee_id(+)
--CONNECT BY prior /* 상향식 */ a.manager_id = a.employee_id;
CONNECT BY  /* 하향식 */ a.manager_id = prior a.employee_id;