[Oracle] DB JOIN

 

조인 (JOIN)

두개 이상의 테이블에서 찾고자하는 데이터를 검색하기 위해서 사용한다.

king의 부서 이름을 출력

==> 조인을 사용하지 않을 경우

-> SELECT employee_id, department_id 
     FROM employees 
     WHERE last_name = 'King';

-> SELECT department_id, department_name 
     FROM departments 
     WHERE department_id IN(80, 90);

==> 조인을 사용할 경우

조인 사용방법 

1.내가 원하는 데이터가 무엇인가?
2.원하는 데이터가 어느 테이블에 있는가?
3.여러 테이블에 있다면 각각의 테이블의 공통컬럼을 찾는다.

->SELECT e.employee_id, e.department_id, d.department_name 
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    AND last_name ='King';


'송강' 교수가 강의하는 과목을 검색

->SELECT p.pname, c.cname
    FROM professor p, course c
    WHERE p.pno = c.pno
    and pname = '송강';

 

학점이 2학점인 과목과 이를 강의하는 교수를 검색

->SELECT p.pname, c.st_num
    FROM professor p , course c
    WHERE p.pno = c.pno
    AND st_num=2;

 

화학과 1학년 학생의 기말고사 성적을 검색하라.

->SELECT s.major, s.syear, c.result
    FROM student s, score c
    WHERE s.sno = c.sno
    AND syear = 1 AND major='화학';

 

화학과 1학년 학생이 수강하는 과목을 검색하라. (3개 테이블 조인)

->SELECT s.major, s.syear, c.cname
    FROM student s, score o, course c
    WHERE s.sno = o.sno AND o.cno = c.cno
    AND syear =1 AND major = '화학'; 

 

---

ANSI JOIN

(이전)
SELECT e.employee_id, e.department_id, d.department_name 
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    AND last_name ='King';

(이후)
->SELECT e.employee_id, e.department_id, d.department_name
    FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
    WHERE last_name ='King';

---

3개 이상 조인

->테이블1 JOIN 테이블2
                  ON 공통컬럼 1 = 공통컬럼 1
                  JOIN 테이블3
                  ON 공통컬럼 2 = 공통컬럼 2

3개이상의 테이블을 조인하여 사원번호, 이메일, 부서번호, 부서이름, 직종번호(job_id), 직종이름(job_title)을 출력


->SELECT e.employee_id, email, e.department_id, d.department_name, j.job_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;


->SELECT e.employee_id, email, e.department_id, d.department_name, j.job_id, j.job_title
    FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
    JOIN jobs j
    ON e.job_id = j.job_id;

 

'Seattle' (city)에 근무하는 사원이름, 부서번호, 직종번호, 직종이름, 도시이름 출력

->SELECT e.last_name, d.department_id, j.job_id, j.job_title, l.location_id, l.city
    FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
    JOIN jobs j
    ON e.job_id = j.job_id
    JOIN LOCATIONS l
    ON d.location_id = l.location_id
    WHERE city = 'Seattle';

 

---

SELF 조인

 'Kochhar' 직속상사의 정보를 출력

->SELECT A.last_name || '의 매니저는 ' ||  B.last_name || '이다.'
    FROM employees A, employees B
    WHERE A.manager_id = B.employee_id
    AND A.last_name = 'Kochhar';

 

학생 중에 동명이인을 검색하라.

->SELECT DISTINCT A.sno, A.sname
    FROM student A, student B
    WHERE A.sname = B.sname
    AND  A.sno != B.sno;

 

---

OUTER 조인(외부 조인) 

데이터값이 null일지라도 누락없이 데이터값을 가져오기 위해 사용한다. 

 

->SELECT * FROM employees;   
>> 107row

->SELECT *
    FROM employees e, departments d
    WHERE e.department_id = d.department_id;  
 >> 106row, department_id null값이 포함,  1row 누락발생

 

OUTER JOIN 적용

->SELECT *
    FROM employees e, departments d
    WHERE e.department_id = d.department_id(+);

>> 107row , null임에도 불구하고 출력

*ANSI JOIN (OUTER JOIN)

->SELECT *
    FROM employees e LEFT JOIN departments d
    ON e.department_id = d.department_id;

>> 107row 


등록된 과목에 대한 모든 교수를 검색(등록하지 않아도 모든 교수 출력)

->SELECT * FROM professor;      >>36row
->SELECT * FROM course;          >>32row

OUTER JOIN
->SELECT c.cno, p.pname
    FROM professor p, course c
    WHERE c.cno(+) = p.cno;

OUTER JOIN (row가 많은 쪽(professor, LEFT)을 기준으로 JOIN)

->SELECT cname, pname
    FROM professor p LEFT JOIN course c
    ON p.pno = c.pno;                 >> 36row

FULL JOIN (양쪽 NULL 모두 출력)
SELECT cname, pname
    FROM professor p FULL JOIN course c
    ON p.pno = c.pno;