국비 지원 개발자 과정_Day31

JOIN

둘 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 데이터를 검색하는 방법

연결을 위해 적어도 하나의 칼럼을 공유하고 있어야 함

보통 Primary Key 및 Foreign Key 값을 사용하여 조인함

 

INNER JOIN

키 값이 있는 테이블의 컬럼 값을 비교 후 조건에 맞는 값을 가져오는 것

두 테이블을 조인할 때, 두 테이블에 지정한 열의 데이터가 있어야 함

서로 연관된 내용만 검색하는 조인 방법

 

OUTER JOIN

두 테이블을 조일할 때, 1개의 테이블에만 데이터가 있어도 결과가 나옴

조인 조건이 정확히 일치하지 않아도 모두 조회하기에, 동등 조인 조건을 만족하지 못하고 누락된 행을 출력할 때 사용

조인 시 값이 없는 조인 측에 "(+)"를 위치 시킴

 

SELF JOIN

자신이 자신과 조인한다는 의미로 1개의 테이블을 사용

같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것처럼 조인함

 

/* Formatted on 2023/01/09 오후 5:25:35 (QP5 v5.215.12089.38647) */
SELECT * FROM emp;

SELECT * FROM dept;

SELECT empno, ename, deptno FROM emp;

--사원집합의 부서명은 존재하지 않는 컬럼명
--제3정규화를 지키기 위한 테이블 설계
--부서이름은 부서번호에 종속적이지 사원번호에 종속적이지 않다 - 강제한다면 반정규화, 역정규화
--SELECT empno, ename, deptno, dname
--FROM emp;

--카타시안의 곱은 나올 수 있는 모든 경우의 수가 출력 - 쓰레기값이 포함됨
SELECT empno,
       ename,
       dept.deptno,
       dname
  FROM emp, dept;

--Join이란?
--둘 이상의 테이블을 연결하여 데이터를 검색
--보통 둘 이상의 행들의 공통된 값을 기준으로 연결(Primary key 혹은 Foreign key)

--공통된 컬럼을 비교한다 = 등가조인(equal join)
--부서집합과 사원집합은 서로 상속관계에 있다
--두 집합의 관계형태는 1:n 관계
SELECT empno,
       ename,
       dept.deptno,
       dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

SELECT empno,
       ename,
       deptno,
       dname
  FROM emp NATURAL JOIN dept;

-- 결국 inner join도 양쪽에 존재하는 값들만 검색되는 것이다
SELECT empno,
       ename,
       dept.deptno,
       dname
  FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

--실제로 데이터를 찾는 것은 옵티마이저가 함 -> 일하는 기준(순서)이 있다
--2개 이상의 테이블이 오는 경우 -> join
--어떤 집합을 먼저 드라이브 하는가에 따라서 검색 속도가 달라질 수 있다(3초안에 조회결과가 나와야함)
--속도문제 -> 순서와 관계 -> 실행계획을 통해 확인 가능
--DBMS가 실행계획을 세우고 검색하는 컨셉을 조건을 수렴하는 모든 로우의 값을 가장 빨리
--찾아낼 수 있는 방법으로 실행계획을 세워달라는 것

--실행계획 -> all_rows
--해쉬조인 방식으로 데이터 검색
--테이블을 각각 먼저 따로 스캔하고 조건을 따지면서 결과를 출력함
SELECT empno,
       ename,
       dept.deptno,
       dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

--힌트문을 적으면 옵티마이저에게 rul base 옵티마이저 모드 실행계획을 세우라는 의미
--실행계획 - Hint: Rule
-- CBO방식에서 정한 순서와 다르게 검색한다.
-- EMP테이블 풀 스캔 -> dept deptno 스캔 -> 사원테이블 deptno와 같은지 비교 -> 같은조건 검색
SELECT /*+rule*/
      empno,
       ename,
       dept.deptno,
       dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

-- 18자의 rowid로 검색 가능
SELECT ROWID rno FROM emp;

SELECT ename
  FROM emp
 WHERE ROWID = 'AAARE8AAEAAAACUAAB';

SELECT empno, ename
  FROM emp
 WHERE empno = 7499;

--인덱스를 읽어서 처리함, 자동정렬
SELECT empno FROM emp;

--테이블을 읽어서 처리함
SELECT ename FROM emp;

--정렬 후 테이블을 읽음
  SELECT ename
    FROM emp
ORDER BY ename ASC;

--SELECT문 처리 순서
--1. 파싱(parsing) - 문법이 맞는지 여부
--2. DBMS 실행계획 세움
--3. 실행계획을 옵티마이저에게 넘김
--4. open...cursor(이동의미->데이터존재true or 없음false)...fetch...close(위변조방지)

--같은 select문을 두 번 연속하여 요청하면 1, 2단계는 생략하고 3단계부터 진행(속도가 빠름)
SELECT * FROM salgrade;

SELECT e.ename, e.sal, s.grade
  FROM emp e, salgrade s
 WHERE e.sal BETWEEN s.losal AND s.hisal;

--temp와 emp_level을 이용해 emp_level의 과장 직급의 연봉 상한/하한 범위 내에드는
--직원의 사번과, 성명, 직급, salary를 읽어보자.
SELECT * FROM emp_level;

SELECT * FROM temp;

--어떤 테이블에서 읽어오는가에 따라 다른 의미가 되기도함
  SELECT t.emp_id AS "사번",
         t.emp_name AS "성명",
         t.lev AS "직급",
         t.salary AS "연봉"
    FROM temp t, emp_level e
   WHERE e.lev = '과장' AND t.salary BETWEEN e.from_sal AND e.to_sal
ORDER BY t.salary DESC;

--tcom의 work_year = '2001'인 자료와 temp를 사번으로 연결해서 join한 후
--comm을 받는 직원의 성명, salary + COMM을 조회해 보시오.
SELECT * FROM tcom;

SELECT * FROM temp;

  SELECT t.emp_name "이름", t.salary + c.comm "연봉"
    FROM temp t, tcom c
   WHERE t.emp_id = c.emp_id AND c.work_year = '2001'
ORDER BY t.salary + c.comm DESC;

--natural join 사용
  SELECT t.emp_name "이름", t.salary + c.comm "연봉"
    FROM temp t NATURAL JOIN tcom c
   WHERE c.work_year = '2001'
ORDER BY t.salary + c.comm DESC;

--외래키는 중복이 허용됨
--외래키는 인덱스가 제공되지 않음
--외래키는 부모테이블과 자손테이블의 join시 사용됨
--컬럼정보를 출력할때에 index가 존재하는 컬럼을 조회하는 것이 빠르다
--왜냐하면 인덱스만 읽고도 데이터를 가져오니까
--그리고 기본적으로 오름차순 정렬이 되어있어서 검색속도에 영향이 있으니까
--그렇기에 아래의 경우 deptno를 e보다 d에서 가져오는 것이 빠르다
SELECT d.deptno, ename
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;

--테이블이 관리하는 데이터가 어떤 상태일 때 outer join이 필요한가?
--deptno 40이 나옴
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

--아무 값도 나오지 않음
SELECT deptno FROM emp
MINUS
SELECT deptno FROM dept;

--양쪽 다 존재하는 것이 나오기에 deptno 40이 나오지 않음
SELECT empno,
       ename,
       dept.deptno,
       dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

SELECT DISTINCT (deptno) FROM emp;

--Outer Join
--Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 테이터를 반환하지 못한다.
--동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.
--Outer Join 연산자는 "(+)" 이다. 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.

--40번 부서번호와 부서명도 같이 출력해야한다면?
--두 개 이상의 테이블(emp, dept) 조인시 한 쪽 테이블(dept)의 행(40)에 대해
--다른 쪽 테이블(emp)에 일치하는 행(10, 20, 30)이 없더라도
--다른 쪽 테이블(emp)의 행을 NULL로하여 행을 리턴하는 것

--emp에 deptno 40번이 없으니 (+)을 달아준다
SELECT d.deptno, ename
  FROM dept d, emp e
 WHERE d.deptno = e.deptno(+);

--각 사번의 성명, 이름, salary, 연봉하한금액, 연봉상한금액을 보고자 한다.
--temp와 emp_level을 조인하여 결과를 보여주되, 연봉의 상하한이 등록되어 있지 않은
--'수습' 사원은 성명, 이름, salary 까지만이라도 나올 수 있도록 쿼리를 작성하시오.
SELECT * FROM temp;

SELECT * FROM emp_level;

  SELECT t.lev "직급",
         t.emp_name "이름",
         t.salary "연봉",
         e.from_sal "연봉하한",
         e.to_sal "연봉상한"
    FROM temp t, emp_level e
   WHERE t.lev = e.lev(+)
ORDER BY t.salary DESC, t.emp_name ASC;

--t_worktime 이들 데이터의 작업시간이 짧게 걸리는 시간 순서대로
--1부터 15까지의 순위를 매겨서 출력하시오.
SELECT * FROM t_worktime;

SELECT ROWNUM rno, deptno FROM dept;

SELECT ROWNUM rno, ename FROM emp;

SELECT *
  FROM t_worktime
 WHERE ROWNUM < 4;

--카타시안의 곱을 이용해서 데이터를 3배수로 만든다
--3배수중에서 나를 기준으로 작업시간이 빠른 값을 남긴다
--남겨진 빠른 작업시간 값을 센다
--위에 1을 더한 숫자가 나의 순위이다.
SELECT *
  FROM (SELECT seq_vc, workcd_vc, time_nu
          FROM t_worktime
         WHERE ROWNUM < 4) a,
       (SELECT seq_vc, workcd_vc, time_nu
          FROM t_worktime
         WHERE ROWNUM < 4) b
 WHERE a.time_nu > b.time_nu;

SELECT *
  FROM (SELECT seq_vc, workcd_vc, time_nu
          FROM t_worktime
         WHERE ROWNUM < 4) a,
       (SELECT seq_vc, workcd_vc, time_nu
          FROM t_worktime
         WHERE ROWNUM < 4) b
 WHERE a.time_nu >= b.time_nu(+);

  SELECT a.workcd_vc, a.time_nu
    FROM (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM < 4) a,
         (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM < 4) b
   WHERE a.time_nu >= b.time_nu(+)
GROUP BY a.workcd_vc, a.time_nu;

  SELECT a.workcd_vc, a.time_nu, COUNT (b.time_nu) AS "rank"
    FROM (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM < 4) a,
         (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM < 4) b
   WHERE a.time_nu >= b.time_nu(+)
GROUP BY a.workcd_vc, a.time_nu
ORDER BY COUNT (b.time_nu) ASC;

  SELECT a.workcd_vc, a.time_nu, COUNT (b.time_nu) AS "rank"
    FROM (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM <= 15) a,
         (SELECT seq_vc, workcd_vc, time_nu
            FROM t_worktime
           WHERE ROWNUM <= 15) b
   WHERE a.time_nu >= b.time_nu(+)
GROUP BY a.workcd_vc, a.time_nu
ORDER BY COUNT (b.time_nu) ASC;

SELECT workcd_vc, time_nu, RANK () OVER (ORDER BY time_nu) rnk
  FROM t_worktime;

--rownum에 대한 연습문제
--각 행에 1학년 부터 4학년 까지를 분리해서 한 행에 하나의 학년만 나오도록
--하고자 한다. 현재 12행이 있으니 총 48행의 결과가 대학별/학과별로 인원수가 나와야한다
--컬럼레벨의 값을 로우 레벨로 옮겨 출력하기(사용빈도 높음)
SELECT * FROM test11;

SELECT ROWNUM rno
  FROM emp
 WHERE ROWNUM < 5;

SELECT *
  FROM test11,
       (SELECT ROWNUM rno
          FROM emp
         WHERE ROWNUM < 5);

  SELECT dept,
         DECODE (rno,
                 1, '1학년',
                 2, '2학년',
                 3, '3학년',
                 4, '4학년')
    FROM test11,
         (SELECT ROWNUM rno
            FROM emp
           WHERE ROWNUM < 5)
ORDER BY dept ASC,
         DECODE (rno,
                 1, '1학년',
                 2, '2학년',
                 3, '3학년',
                 4, '4학년') ASC;

--컬럼레벨 늘어남
SELECT 1, 2, 3 FROM DUAL;

--로우레벨 늘어남
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

  SELECT dept,
         DECODE (rno,
                 1, '1학년',
                 2, '2학년',
                 3, '3학년',
                 4, '4학년'),
         DECODE (rno,  1, fre,  2, sup,  3, jun,  4, sen)
    FROM test11,
         (SELECT ROWNUM rno
            FROM emp
           WHERE ROWNUM < 5)
ORDER BY dept ASC,
         DECODE (rno,
                 1, '1학년',
                 2, '2학년',
                 3, '3학년',
                 4, '4학년') ASC;

--test11의 자료를 이용하여 1,2학년과 3,4학년의 인원수가 각각 같은 줄에 나오도록
--query를 작성해 보라.
  SELECT dept, DECODE (rno,  1, '1,2학년',  2, '3,4학년')
    FROM test11,
         (SELECT ROWNUM rno
            FROM emp
           WHERE ROWNUM < 3)
ORDER BY dept ASC;

  SELECT dept,
         DECODE (rno,  1, '1,2학년',  2, '3,4학년'),
         DECODE (rno,  1, fre,  2, jun) "1,3학년",
         DECODE (rno,  1, sup,  2, sen) "2,4학년"
    FROM test11,
         (SELECT ROWNUM rno
            FROM emp
           WHERE ROWNUM < 3)
ORDER BY dept ASC, DECODE (rno,  1, '1,2학년',  2, '3,4학년');

  SELECT dept,
         DECODE (rno,  1, '1,2학년',  2, '3,4학년'),
         DECODE (rno,  1, fre + sup,  2, jun + sen)
    FROM test11,
         (SELECT ROWNUM rno
            FROM emp
           WHERE ROWNUM < 3)
ORDER BY dept ASC;

--sam_tab02를 이용해 한 로우에 4개씩 나란히 표시하기
SELECT * FROM sam_tab02;

SELECT ROWNUM rno, gubun FROM sam_tab02;

SELECT rno, gubun
  FROM (SELECT ROWNUM rno, gubun FROM sam_tab02);

--CEIL(rno/4) cno - rownum을 가공하여 몇번째 튜플의 인덱스 값인지 판단
SELECT CEIL (rno / 4) cno, rno, gubun
  FROM (SELECT ROWNUM rno, gubun FROM sam_tab02);

SELECT CEIL (rno / 4) cno,
       MOD (rno, 4) mno,
       rno,
       gubun
  FROM (SELECT ROWNUM rno, gubun FROM sam_tab02);

SELECT CEIL (rno / 4) cno,
       MOD (rno, 4) mno,
       DECODE (MOD (rno, 4), 1, gubun) c1,
       gubun
  FROM (SELECT ROWNUM rno, gubun FROM sam_tab02);

SELECT CEIL (rno / 4) cno,
       MOD (rno, 4) mno,
       DECODE (MOD (rno, 4), 1, gubun) c1,
       DECODE (MOD (rno, 4), 2, gubun) c2,
       DECODE (MOD (rno, 4), 3, gubun) c3,
       DECODE (MOD (rno, 4), 0, gubun) c4
  FROM (SELECT ROWNUM rno, gubun FROM sam_tab02);

  SELECT CEIL (rno / 4) cno
    FROM (SELECT ROWNUM rno, gubun FROM sam_tab02)
GROUP BY CEIL (rno / 4)
ORDER BY CEIL (rno / 4) ASC;

  SELECT CEIL (rno / 4) cno,
         MAX (DECODE (MOD (rno, 4), 1, gubun)) c1,
         MAX (DECODE (MOD (rno, 4), 2, gubun)) c2,
         MAX (DECODE (MOD (rno, 4), 3, gubun)) c3,
         MAX (DECODE (MOD (rno, 4), 0, gubun)) c4
    FROM (SELECT ROWNUM rno, gubun FROM sam_tab02)
GROUP BY CEIL (rno / 4)
ORDER BY CEIL (rno / 4) ASC;

--temp자료를 이용해 한 행에 3명의 사번과 성명을 보여주는 쿼리를 작성하시오.
SELECT * FROM temp;

SELECT emp_id, emp_name, ROWNUM rno FROM temp;

  SELECT CEIL (rno / 3) cno,
         MAX (DECODE (MOD (rno, 3), 1, emp_id)) 사번1,
         MAX (DECODE (MOD (rno, 3), 1, emp_name)) 이름1,
         MAX (DECODE (MOD (rno, 3), 2, emp_id)) 사번2,
         MAX (DECODE (MOD (rno, 3), 2, emp_name)) 이름2,
         MAX (DECODE (MOD (rno, 3), 0, emp_id)) 사번3,
         MAX (DECODE (MOD (rno, 3), 0, emp_name)) 이름3
    FROM (SELECT ROWNUM rno, emp_id, emp_name FROM temp)
GROUP BY CEIL (rno / 3)
ORDER BY CEIL (rno / 3) ASC;

--row레벨의 값을 컬럼레벨에 올려 출력하기
SELECT *
  FROM emp NATURAL JOIN dept;

SELECT DECODE (job, 'CLERK', sal, NULL) FROM emp;

SELECT SUM (DECODE (job, 'CLERK', sal, NULL)),
       SUM (DECODE (job, 'MANAGER', sal, NULL)),
       SUM (DECODE (job,  'CLERK', NULL,  'MANAGER', NULL,  sal)),
       SUM (sal)
  FROM emp;

  SELECT deptno,
         SUM (DECODE (job, 'CLERK', sal, NULL)),
         SUM (DECODE (job, 'MANAGER', sal, NULL)),
         SUM (DECODE (job,  'CLERK', NULL,  'MANAGER', NULL,  sal)),
         SUM (sal)
    FROM emp
GROUP BY deptno;

SELECT 1 rno FROM DUAL
UNION ALL
SELECT 2 FROM DUAL;

--카타시안의 곱 만들기
SELECT *
  FROM (  SELECT deptno,
                 SUM (DECODE (job, 'CLERK', sal, NULL)) clerk급여,
                 SUM (DECODE (job, 'MANAGER', sal, NULL)) manager급여,
                 SUM (DECODE (job,  'CLERK', NULL,  'MANAGER', NULL,  sal))
                    그외급여,
                 SUM (sal) 부서급여
            FROM emp
        GROUP BY deptno) a,
       (SELECT 1 rno FROM DUAL
        UNION ALL
        SELECT 2 FROM DUAL) b;

--group by로 묶기
  SELECT DECODE (rno,  1, dname,  2, '총계')
    FROM (  SELECT dname,
                   SUM (DECODE (job, 'CLERK', sal, NULL)) clerk급여,
                   SUM (DECODE (job, 'MANAGER', sal, NULL)) manager급여,
                   SUM (DECODE (job,  'CLERK', NULL,  'MANAGER', NULL,  sal))
                      그외급여,
                   SUM (sal) 부서급여
              FROM emp, dept
             WHERE emp.deptno = dept.deptno
          GROUP BY dname) a,
         (SELECT 1 rno FROM DUAL
          UNION ALL
          SELECT 2 FROM DUAL) b
GROUP BY DECODE (rno,  1, dname,  2, '총계')
ORDER BY DECODE (rno,  1, dname,  2, '총계') ASC;

  SELECT DECODE (rno,  1, dname,  2, '총계') AS "부서명",
         SUM (clerk_sal),
         SUM (manager_sal),
         SUM (etc_sal),
         SUM (dept_sal)
    FROM (  SELECT dname,
                   SUM (DECODE (job, 'CLERK', sal, NULL)) clerk_sal,
                   SUM (DECODE (job, 'MANAGER', sal, NULL)) manager_sal,
                   SUM (DECODE (job,  'CLERK', NULL,  'MANAGER', NULL,  sal))
                      etc_sal,
                   SUM (sal) dept_sal
              FROM emp, dept
             WHERE emp.deptno = dept.deptno
          GROUP BY dname) a,
         (SELECT 1 rno FROM DUAL
          UNION ALL
          SELECT 2 FROM DUAL) b
GROUP BY DECODE (rno,  1, dname,  2, '총계')
ORDER BY DECODE (rno,  1, dname,  2, '총계') ASC;

--self join

--관계형태 중에서 n:n은 조인을 걸지 않는다
--왜냐하면 쓰레기값이 포함되어 있으니까
--업무에대한 정의가 덜 되어있다.
--학생과 교과목, 회원과 상품의 관계, 회원과 책 -> n:n관계 -> 교차엔티티(행위엔티티)
--1:1, 1:n 고려대상

--tdept테이블에 자신의 상위 부서 정보를 관리하고 있다.
--이 테이블을 이용하여 부서코드, 부서명, 상위부서코드, 상위부서명을 읽어오는 쿼리를 만들어 보자.
SELECT * FROM tdept;

SELECT *
  FROM tdept a, tdept b
 WHERE a.dept_code = b.parent_dept;

SELECT b.dept_code AS "부서코드",
       b.dept_name AS "부서명",
       a.dept_code AS "상위부서코드",
       a.dept_name AS "상위부서명"
  FROM tdept a, tdept b
 WHERE a.dept_code = b.parent_dept;