13.JOIN

-- 테이블 이름 변경
RENAME DEPARTMENT TO DEPT;

/*
    <JOIN>
    - 두 개 이상의 테이블에서 데이터를 조회하고자 할 때 사용하는 구문
    - 조건을 주지 않으면 카티전 곱 발생 -> 행 * 행
    *카티전 곱 : 집합 A의 원소 a와 집합 B의 원소 b를 순서대로 벌여 놓고 괄호로 묶은 순서쌍 (a, b) 전체의 집합*
        1)등가조인(EQUAL JOIN) or 내부조인(INNER JOIN)
        - 연결시키는 칼럼의 값이 일치하는 행들만 조인돼서 조회함
          (일치하는 값이 없는 행은 조회X)
          (1)오라클 전용 구문
                SELECT 컬럼, 컬럼 ...
                FROM 테이블1, 테이블2
                WHERE 테이블1.컬럼명 = 테이블2.컬럼명;
                 
                - FROM절에 조회하고자 하는 컬럼들을 ,(콤마)로 구분하여 나열
                - WHERE절에 매칭시킬 컬럼에 대한 조건을 제시
          (2)ANSI 표준 구문
                SELECT 컬럼, 컬럼 ...
                FROM 테이블1
                [INNER] JOIN 테이블2 ON (테이블명1.컬럼명 = 테이블2.컬럼명);
                [INNER] JOIN 테이블2 USING (컬럼명);
                - FROM절에 기준이 되는 테이블을 기술
                - JOIN절에 같이 조회하고자 하는 테이블을 기술 후 조건을 명시
                - 연결에 사용하려는 컬럼명이 같은 경우 ON절 대신 USING(컬럼명)을 사용
                *ANSI SQL*
                  DMBS들에서 각기 다른SQL을 사용하므로, 미국 표준 협회에서 이를 표준화하여 표준SQL문을 정립 시켜 놓은 것
*/

-- 각 사원들의 사번, 사원명, 부서코드, 부서명을 조회
SELECT COUNT(*) FROM EMP; -- 23건
SELECT emp_id, emp_name, dept_code FROM emp;
SELECT COUNT(*) FROM dept; -- 9건
SELECT dept_id, dept_title FROM Dept;

-- 카티전 곱 emp 행23 * dept 행 9개 = 총 207개의 행이 결과로 나옴
SELECT *
FROM Emp, Dept;

-- 오라클 구문
-- 1)연결할 컬럼이 다른 경우
SELECT emp_id, emp_name, dept_id, dept_title
FROM Emp, Dept
WHERE dept_code = dept_id; -- 21건
-- *위 쿼리를 실행하고 나온 결과를 결과집합(ResultSet)이라고 함
-- INNER JOIN : 조인 조건에 만족하는 값만 조회
-- 사원 테이블에서 누락된 데이터 찾기
-- 전체 사원의 결과집합 - 조인문의 결과집합
-- emp테이블 제외 : dept_id에 null
SELECT *
FROM emp
MINUS
SELECT emp.* -- emp테이블이 가지고 있는 모든 컬럼을 조회
FROM Emp, Dept
WHERE dept_code = dept_id; -- JOIN 조건
 
-- 서브쿼리 이용해서 JOIN결과 중복을 제거 후 부서테이블에 사용되지 않은 부서코드 찾기
SELECT *
FROM dept
WHERE dept_id NOT IN(SELECT DISTINCT(dept_id)
                    FROM Emp, dept
                    WHERE dept_code = dept_id
);

-- ANSI 표준 구문 사용
SELECT emp_id, emp_name, dept_code, dept_title
FROM Emp
JOIN Dept ON(dept_code = dept_id);

-- 각 사원들의 사번, 사원명, 직급코드, 직급명을 조회
-- ANSI 구문 USING
-- USING을 사용할 경우 테이블명.컬럼명 사용 불가(식별자 사용X)
SELECT emp_id, emp_name, job_code, job_name
FROM Emp
JOIN Job USING(job_code);
-- ANSI 구문 ON
SELECT emp_id, emp_name, Emp.job_code, job_name
FROM Emp
JOIN Job ON(Emp.job_code = Job.job_code);
-- 오라클 구문 *컬럼명이 동일한 경우 테이블명을 명시해야 한다. 그렇지 않으면 에러(열의 정의가 애매합니다)
-- 테이블에 별칭을 사용한 경우 SELECT절에서 별칭을 이용해서 접근해야함
SELECT emp_id, emp_name, E.job_code, job_name
FROM Emp E, Job J
WHERE E.job_code = J.job_code;

-- emp 테이블과 job 테이블을 조인하여 직급이 대리인 사원의 사번,사원명,직급명 급여를 조회
SELECT emp_id, job_name, emp_name, salary
FROM Emp E, Job J
WHERE E.job_code = J.job_code AND job_name = '대리';
-- ANSI ON
SELECT emp_id, job_name, emp_name, salary
FROM Emp
JOIN Job ON(Emp.job_code = Job.job_code)
WHERE job_name = '대리';
-- ANSI USING
SELECT emp_id, job_name, emp_name, salary
FROM Emp
JOIN Job USING(job_code)
WHERE job_name = '대리';

-- 외부조인
SELECT emp_id, emp_name, dept_id, dept_title
FROM Emp, Dept
WHERE dept_code = dept_id(+);

SELECT emp_id, emp_name, dept_id, dept_title
FROM Emp, Dept
WHERE dept_code(+) = dept_id;

/*
    2. 다중JOIN
        - 여러개의 테이블을 조인하는 경우 사용
*/
-- 사번, 사원명, 부서명, 지역명 조회
-- 오라클 구문
-- 다중조인 순서 상관 없음
SELECT emp_id, emp_name, dept_title, local_name
FROM emp, dept, location
WHERE dept_id=dept_code AND location_id=local_code;
-- ANSI(JOIN 두번)
-- 다중조인의 경우 순서 주의
SELECT emp_id, emp_name, dept_title, local_name
FROM emp
INNER JOIN dept ON(dept_id=dept_code) -- INNER조인 생략 가능
JOIN location ON(location_id=local_code); -- 아무것도 안쓰면 INNER조인 

/*
    3. 외부조인(OUTER JOIN)
        - 테이블간에 JOIN시 조건에 일치하지 않는 행도 포함시켜서 조회
        - 기준이 되는 테이블을 지정해야 함(LEFT/LIGHT/(+))
        - ANSI : LEFT/RIGHT
        - 오라클 : (+)
*/
-- 1)LEFT [OUTER] JOIN
--      두 테이블 중 왼편에 기술된 테이블의 컬럼을 기준으로 JOIN을 진행
--      JOIN조건이 일치하지 않아도 왼쪽테이블을 모두 출력
-- emp테이블의 dept_code가 null인 행을 출력하고 싶으면 다른 쪽 테이블에 (+)
SELECT emp_id, emp_name, dept_id, dept_title
FROM emp, dept
WHERE emp.dept_code = dept.dept_id(+);
-- ANSI
SELECT emp_id, emp_name, dept_id, dept_title
FROM emp
LEFT OUTER JOIN dept ON (dept_code = dept_id) ; -- OUTER 생략 가능

-- 2)RIGHT [OUTER] JOIN
--      두 테이블 중 오른편에 기술된 테이블의 컬럼을 기준으로 JOIN을 진행
--      오른쪽 테이블을 모두 출력
-- 사원이름, 부서명, 급여를 출력하는데 부서 테이블의 모든 데이터가 출력되도록
-- ANSI
SELECT emp_name, dept_title, salary
FROM emp
RIGHT JOIN dept ON(dept_code = dept_id);
--ORACLE
SELECT emp_name, dept_title, salary
FROM emp, dept
WHERE dept_code(+) = dept_id;

-- 3)FULL [OUTER] JOIN : 두 테이블이 가지는 모든 행을 조회
--      오라클 구문은 지원하지 않음
-- 21건 조회조건 만족, 부서코드가 NULL인 2건, 사원이 배정되지않은 부서코드 3건 총 26건
SELECT emp_name, dept_title, salary
FROM emp
FULL JOIN dept ON(dept_code = dept_id);

 

/*
5. 비등가 조인(NON EQUAL JOIN) 
    - 조인 조건에 등호(=)를 사용하지 않는 조인문.
      지정한 컬럼값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식.
      (비교 연산자 >, <, >=, <=, BETWEEN AND, IN, NOT IN 등을 사용)
*/
-- 급여등급 테이블
CREATE TABLE SAL_GRADE (
    SAL_LEVEL CHAR(2),
    MIN_SAL NUMBER,
    MAX_SAL NUMBER
);

COMMENT ON COLUMN SAL_GRADE.SAL_LEVEL IS '급여등급';
COMMENT ON COLUMN SAL_GRADE.MIN_SAL IS '최소급여';
COMMENT ON COLUMN SAL_GRADE.MAX_SAL IS '최대급여';
COMMENT ON TABLE SAL_GRADE IS '급여등급';

INSERT INTO sal_grade VALUES ('S1', 6000000, 10000000);
INSERT INTO sal_grade VALUES ('S2', 5000000, 5999999);
INSERT INTO sal_grade VALUES ('S3', 4000000, 4999999);
INSERT INTO sal_grade VALUES ('S4', 3000000, 3999999);
INSERT INTO sal_grade VALUES ('S5', 2000000, 2999999);
INSERT INTO sal_grade VALUES ('S6', 1000000, 1999999);

COMMIT;
SELECT * FROM SAL_GRADE;

-- EMP 테이블과 SAL_GRADE 테이블을 비등가 조인하여 
-- 사원명, 급여, 급여 등급 조회
-- 오라클 구문
SELECT  EMP_NAME, SALARY, SAL_LEVEL
FROM    EMP, SAL_GRADE
WHERE   SALARY BETWEEN MIN_SAL AND MAX_SAL
ORDER BY EMP_ID;

-- 범위가 중복되는 경우 사원이 여러 급여등급을 가지게 되어 중복되어 출력
UPDATE SAL_GRADE SET MAX_SAL = 2999999 WHERE SAL_LEVEL = 'S5';

-- ANSI
SELECT  EMP_NAME, SALARY, SAL_LEVEL
FROM    EMP
JOIN    SAL_GRADE ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);

/*
6. 자체 조인(SELF JOIN)
    같은 테이블을 다시 한번 조인하는 경우에 사용한다.
*/
--  MANAGER_ID : 사수의 사번
-- 사번, 사원명, 부서 코드, 사수 사번, 사수 이름
SELECT  E.EMP_ID 사번, E.EMP_NAME 사원명
        , E.DEPT_CODE 부서코드, E.MANAGER_ID 매니져번호
        , M.EMP_ID 매니져번호M, M.EMP_NAME 매니져이름M
FROM    EMP E, EMP M
WHERE   E.MANAGER_ID = M.EMP_ID(+);

-- ANSI 구문
SELECT  E.EMP_ID 사번, E.EMP_NAME 사원명
        , E.DEPT_CODE 부서코드, E.MANAGER_ID 매니져번호
        , M.EMP_ID 매니져번호M, M.EMP_NAME 매니져이름M
FROM    EMP E 
LEFT /*OUTER*/ JOIN    EMP M ON (E.MANAGER_ID = M.EMP_ID);

-- 실습문제
-- 보너스를 받는 사원들의 사번, 사원명, 보너스, 부서명을 조회
-- 오라클
SELECT  EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM    EMP, DEPT
WHERE   DEPT_CODE = DEPT_ID
AND     BONUS IS NOT NULL;
-- ANSI
SELECT  EMP_ID, EMP_NAME, BONUS, DEPT_TITLE
FROM    EMP
JOIN    DEPT ON (DEPT_CODE=DEPT_ID)
WHERE   BONUS IS NOT NULL;

-- 인사관리부가 아닌 사원들의 사원명, 부서명, 급여를 조회
SELECT  EMP_NAME, DEPT_TITLE, SALARY
FROM    EMP, DEPT
WHERE   EMP.DEPT_CODE = DEPT.DEPT_ID(+)
AND     DEPT_TITLE != '인사관리부';

-- ANSI
SELECT  EMP_NAME, NVL(DEPT_TITLE,'부서없음'), DEPT_CODE, SALARY
FROM    EMP 
LEFT OUTER JOIN    DEPT ON (DEPT_CODE = DEPT_ID)
WHERE NVL(DEPT_TITLE,'부서없음') != '인사관리부';

-- 사번, 사원명, 부서명, 지역명, 국가명 조회
-- 누락되는 사원 없이 조회
SELECT e.emp_id, e.emp_name, d.dept_title, l.local_name, n.national_name
FROM Emp e, Dept d,Location l, National n
WHERE e.dept_code=d.dept_id(+)
AND d.location_id=l.local_code(+)
AND l.national_code=n.national_code(+);

-- ANSI
SELECT e.emp_id, e.emp_name, d.dept_title, l.local_name, n.national_name
FROM Emp e
LEFT JOIN Dept d ON(dept_code=dept_id)
LEFT JOIN Location l ON(location_id=local_code)
LEFT JOIN National n ON(l.national_code=n.national_code);

-- 사번, 사원명, 부서명, 지역명, 국가명, 급여 등급 조회
SELECT e.emp_id, e.emp_name, d.dept_title, l.local_name, s.sal_level
FROM Emp e, Dept d, Location l, Sal_Grade s
WHERE e.dept_code = d.dept_id(+)
AND d.location_id = l.local_code(+)
AND salary BETWEEN min_sal AND max_sal(+);

-- 종합실습문제
-- 1. 직급이 대리이면서 ASIA 지역에서 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역, 급여 조회
SELECT e.emp_id, e.emp_name, j.job_name, d.dept_title, l.local_name, e.salary
FROM Emp e, Job j, Dept d, Location l
WHERE e.job_code = j.job_code
AND e.dept_code = d.dept_id
AND d.location_id = l.local_code
AND j.job_name = '대리' 
AND l.local_name LIKE 'ASIA%';

/*
2) CASE
    CASE WHEN 조건식1 THEN 결과값1
         WHEN 조건식2 THEN 결과값2
         ...
         ELSE 결과값
    END
*/
-- 2. 70년대생 이면서 여자이고, 성이 전씨인 직원들의 사원명, 주민번호, 부서명, 직급명
SELECT emp_name AS 사원, emp_no AS 주민번호, dept_title AS 부서, job_name AS 직급
FROM Emp
LEFT JOIN Dept ON(dept_code = dept_id)
JOIN Job USING(job_code)
--WHERE DECODE(SUBSTR(emp_no,8,1), '1', '남', '3', '남', '2', '여', '4', '여', '확인')='여'
WHERE (SUBSTR(emp_no,8,1)='2' OR SUBSTR(emp_no,8,1)='4')
AND emp_name LIKE '전%'
AND SUBSTR(emp_no,1,1) = '7';

-- 3. 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역을 조회
-- 단, 부서코드가 없는 사원도 출려고딜 수 있게 OUTER JOIN 사용
-- 연봉 : 월급 *12 세자리콤마 표시
SELECT emp_name, NVL(bonus, 0) AS 보너스, TO_CHAR(salary*12,'999,999,999') AS 연봉, dept_title, local_name
FROM Emp
LEFT JOIN Dept ON(dept_code = dept_id)
LEFT JOIN Location ON(location_id = local_code)
--WHERE NVL(bonus, 0) != 0;
WHERE bonus IS NOT NULL;

-- 4. 한국과 일본에서 근무하는 직원들의 사원명, 부서명, 근무지역, 근무국가를 조회
SELECT emp_name, dept_title, local_name, national_name
FROM Emp
JOIN Dept ON(dept_code = dept_id)
JOIN Location ON(location_id = local_code)
JOIN National USING(national_code)
--WHERE  national_code='KO' OR  national_code='JP';
WHERE  national_name IN('한국', '일본');

-- 5. 각 부서별 평균 급여를 조회하여 부서명, 평균급여(정수처리)를 조회
-- 단 부서배치가 안된 사원들의 평균도 같이 나오도록
SELECT NVL(dept_title, '부서없음') AS 부서명, TO_CHAR(ROUND(AVG(salary)), '999,999,999') AS 평균급여
FROM Emp, Dept
WHERE dept_code = dept_id(+)
GROUP BY dept_title;

-- 6. 각 부서별 총 급여의 합이 1000만원이상인 부서의 부서명, 급여의 합
SELECT NVL(dept_title, '부서없음') AS 부서명, TO_CHAR(ROUND(SUM(salary)), '999,999,999') AS 총급여
FROM Emp, Dept
WHERE dept_code = dept_id(+)
HAVING SUM(salary)>=10000000
GROUP BY dept_title;

-- 7. 사번, 사원명, 직급명, 급여등급, 구분을 조회
SELECT emp_id, emp_name, job_name, sal_level,
    CASE 
        WHEN sal_level='S1' OR sal_level='S2' THEN '고급'
        WHEN sal_level IN('S3', 'S4') THEN '중급'
        ELSE '초급'
    END AS 구분
FROM Emp e, Job j, sal_grade s
WHERE e.job_code = j.job_code
AND salary BETWEEN min_sal AND max_sal;

-- 8. 보너스를 받지 않는 직원들 중 직급코드가 J4,J7인 직원들의 사원명, 직급명, 급여 조회
SELECT emp_name, job_name, salary
FROM Emp e, Job j
WHERE e.job_code = j.job_code
AND bonus IS NULL
AND e.job_code IN('J4', 'J7');

-- 9. 부서가 있는 직원들의 사원명, 직급명, 부서명, 근무지역 조회
SELECT emp_name, job_name, dept_title, local_name
FROM Emp
JOIN Dept ON(dept_code = dept_id)
JOIN Job USING(job_code)
JOIN Location ON(location_id = local_code);

-- 10. 해외영업팀에 근무하는 직원들중 2013-01-01 이후 입사자의 사원명, 직급명, 부서코드, 부서명 조회
SELECT emp_name, job_name, dept_id, dept_title
FROM Emp
JOIN Dept ON(dept_code = dept_id)
JOIN Job USING(job_code)
JOIN Location ON(location_id = local_code)
WHERE dept_title LIKE '해외영업%'
AND hire_date>='2013/01/01';

-- 11. 이름에 '형'자가 들어있는 직원들의 사번, 사원명, 직급명
SELECT emp_id, emp_name, job_name
FROM Emp e, Job j
WHERE e.job_code = j.job_code
AND emp_name LIKE '%형%';