Oracle Select (2)

※ Select (JOIN)

JOIN의 정의

두개 이상의 테이블을 연결해서 데이터를 검색하는 방법이다.

보통 두개이상의 행(ROW)들의 공통된 값 Primary Key (기본키), Foreing Key(외래키) 값을

사용해서 JOIN 한다.

  • Primary Key(기본키) : 테이블에서 중복이 되지 않는 키
  • Foreign Key(외래키) : 다른 테이블에서 PK,UK 인 경우가 많다. ``` (중요도)
    1. inner JOIN : 교집합 ★★★★★★
    2. fullouter JOIN
    3. cross JOIN
    4. outer
      left ★★★ rigth ★★★
    5. self JOIN : 같은 테이블의 데이터를 산출 ★★★★★★ ```

JOIN 모형

inner JOIN

  • Ansi SQL(모든 SQL에서 언어가 공통된다) , 오라클 언어 두개 모두 기재한다. 
    SELECT e.employee_id, e.first_name, e.department_id, d.department_id,d.department_name 
FROM employees e INNER JOIN departments d
  ON e.department_id = d.department_id;

    위의 검색의 목적은 d.department_name을 가져오기 위함을 잊지 말자.

FROM절 이후의 불러와야할 테이블을 불러온다(alias 하여 이용하면 가독성이 좋아진다)

  • Oracle 
    SELECT employee_id, first_name,
  e.department_id, d.department_id, d.department_name, d.location_id
FROM employees e, departments d
where e.department_id = d.department_id   ★ 여기까지가 교집합
  and e.first_name = 'Adam';
    1. employees 테이블과 departments 테이블을 불러온다.
    2. WHERE 조건절에서 departments_id가 같다는 조건을 걸어준다.
    3. 그리고 검색때 departments_name이 포함된 departments 테이블에서 탐색하여 출력한다.

Cross JOIN

  • Ansi SQL 
    SELECT e.employee_id, e.first_name,
  e.department_id, d.department_id,
  d.department_name
FROM employees e CROSS JOIN departments d;

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

    Cross JOIN의 경우 모든 사원에게 각 부서별 이름을 붙여주는 형식이다. 거의 잘 쓰이지 않는다.

Full outer join 

SELECT e.employee_id, e.department_id,
     e.department_id, d.department_id,
     d.department_name
FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

Outer JOIN (left, right)

    1. Ansi left 
      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;

      From의 첫번째로 오는 테이블이 기준이되어 LEFT OUTER가 되면 교집합을 제외한 employees 테이블만 남게 된다.

    1. Oracle left 
      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(+);

      (+) 가 붙은 반대쪽 테이블이 기준이 된다.

    1. Oracle right 
      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;

      Self JOIN

  • 동일한 테이블을 JOIN 
    SELECT a.employee_id, a.first_name,
  a.manager_id, b.employee_id,
  b.first_name
FROM employees a, employees b      ★ a : 사원 b : 상사
WHERE a.manager_id = b.employee_id;

    같은 테이블 안에서 사원과 상사를 나누어 탐색한다.

사원의 상사를 구하기 위해 manager_id 와 employee_id이 같은경우 b 테이블에서 불러온 이름의 사람이 상사인것을 확인 할 수 있다.

계층형 구조 (오름,내림) 

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  a.manager_id = PRIOR a.employee_id;

위의 소스코드는 하향식으로 가장 상위에 있는 상사를 아래로 하나씩 정렬되어 출력한다.

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;

상향식이며, 아래 사원의 manager_id를 확인하여 위 상사를 하나씩 출력한다.

Join 예제

문제

EMPLOYEES 테이블에서 left join하여 관리자(매니저)를 출력하고 매니저 아이디가 없는 사람은 배제하고 하향식으로 하며, 급여는 역순으로 출력하라.

SELECT a.employee_id, a.first_name , a.salary, a.manager_id, b.employee_id, b.first_name
FROM employees a, employees b           ★ a: 사원   b: 관리자(사원)
WHERE a.manager_id = b.employee_id(+)   
START WITH a.manager_id is not null
CONNECT BY  a.manager_id = PRIOR a.employee_id    ★ 목적은 연결 PRIOR의 위치에 따라 하향식인지 상향식인지 구분
ORDER BY a.salary DESC;

left joni 하여 사원번호를 중심으로 조건을 걸어준다. 또한 메인 목적이 관리자를 출력하는것이기 때문에 left join 한다.

문제

문제9) EMPLOYEES 테이블에서 right join하여 관리자(매니저)가 108번 상향식으로 급여는 역순으로 출력하라.

SELECT a.employee_id,a.first_name as "사원명", a.salary, a.manager_id, b.employee_id, b.first_name as "상사명"
FROM employees a, employees b
WHERE a.manager_id(+) = b.employee_id and a.manager_id = '108'
CONNECT BY PRIOR a.manager_id = a.employee_id
ORDER BY a.salary DESC;

사원중에서 매니저의 번호가 108번인 사람을 구하는것 이기 때문에 right join을 하여 사원을 구하는것의 목적을 생각하자. 다음 조건인 108 을 WHERE 조건에서 걸어준다.

문제

근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시순으로 정렬하시오. (근무 인원이 0명인 곳도 표시)

SELECT d.loc, count(e.empno)
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno    ★ 공통 부분 JOIN , 근무지는 dept에 있기 때문에 right join을 해주어야 한다.
GROUP BY d.loc
HAVING count(e.empno) <= 5
ORDER BY count(d.loc) ASC;

문제

57) 사원의 이름 및 사원번호를 관리자의 이름과 관리자 번호와 함께 표시하고 각각의 열 레이블은 employee, emp#, manager, mgr#로 지정하는데 King을 포함하여 관리자가 없는 모든 사원을 표시하도록 하고 결과를 사원번호를 기준으로 정렬

SELECT a.ename as employee, a.empno as emp#,b.ename as manager, b.empno as mgr#
FROM emp a, emp b
WHERE a.mgr = b.empno(+)
ORDER BY a.empno ASC;

모든 사원을 표시하라고 했기때문에 OUTER쪽으로 생각할 수 있으며, 사원을 표기함으로 left join으로하여 출력한다.

문제

58) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고 부서번호는 department, 사원이름은 employee, 동일한 부서에서 근무하는 사원은 colleague로 표시하시오. (부서번호, 사원이름,동료 순으로 오름차순 정렬)

SELECT a.empno,a.ename employee, a.deptno department, b.deptno, b.empno colleague, b.ename
FROM emp a , emp b 
WHERE a.deptno = b.deptno
    AND a.empno != b.empno  ★ 자기 자신을 비교 하는것은 안나온다.
ORDER BY a.deptno, a.ename, b.empno ASC;

지정한 사원과 동일한 부서에서 근무하는 사원을 비교 해야하기 때문에 WHERE 절에서 한번 같은 동료로 조건을 걸어준다.

자기 자신과 비교하는 것을 조건으로 제외해주고, 정렬해준다.

문제

59)10번 부서에서 근무하는 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하시오.

SELECT e.deptno, d.dname, e.ename, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno 
    AND e.deptno = 10 
    AND (e.sal >= s.losal And e.sal <= s.hisal);   ★  e.sal BETWEEN s.losal AND s.hisal; 둘다 가능

sal 등급 테이블을 불러와야한다. WHERE 절에서 join, 동일 부서 조건, 그리고 sal의 등급 범위 조건을 걸어준다.