JOIN함수

오라클은 쿼리를 날리면 테이블을 램으로 가져와서 램에서 작업을 한다.
평소에는 하드에 저장하고 모든 테이블을 통으로 램으로 복사해와서 작업테이블 순서와 구조파악이 힘듦
 

조인할시 철칙은 앞테이블이 작아야 한다. 한 테이블이 널값이 들어 있으면 데이타는 나오지 않는다.

조건 where을 빼먹었다...조건을 걸지 않으면 카티션 곱이 된다.
카티션곱은 큰데이타를 필요실 할때 시험적으로 만들어서 사용한다.

 

1. 조인의 개념
두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하게 하는 기능
예를 들어 학번이 10101인 학생의 이름과 소속 학과 이름을 출력하려 할 때
student table 과 department table을 두 번 조회 해야 하지만 조인을 이용하면 한번에 조회가 가능하다.

 

2. 조인의 종류
 카티션 곱(cartesian product, cross join), EQUI JOIN, NON-EQUI JOIN OUTER JOIN, SELF JOIN 등이 있다.

 

3. 조인 문법
SELECT table1.column, table2.column
FROM  table1, table2
WHERE condition;

 

1) 카티션 곱 (Cartesian Product)
  두개 이상의 테이블에 대해 연결 가능한 행을 모두 결합하는 조인방법.
WHERE 절에서 조인 조건절을 생략하거나 조인 조건을 잘못 설정하여 양쪽테이블을 연결하는 조건을 만족하는 행이
하나도 없는 경우에 발생한다.

예제)
학생 테이블과 부서 테이블을 카티션 곱을 한 결과를 출력하여라.

SQL>   SELECT name, student.deptno, dname, loc
            FROM student, department;

위 문장을 실행하면 112개의 행이 나온다.
위 쿼리문에서 WHERE 조건이 없어서생긴 문제이며 항상 이런 상황이 발생하지 않도록 주의해야 한다.

 

2) EQUI JOIN
SQL 문에서 가장 많이 사용되는 조인으로 조인 대상 테이블에서 공통칼럼을  ‘=‘ 비교를 통해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법임.

문법)
 SELECT    table1.column, table2.column
 FROM       table1, table2
 WHERE     table1.column = table2.column;

 

EQUI JOIN 사용 예)
학생 테이블과 부서 테이블을 EQUI JOIN 하여 학번, 이름, 학과번호, 소속학과 이름, 학과 위치를 출력하여라.

SQL>    SELECT s.studno, s.name, s.deptno, d.dname, d.loc
            FROM  student s, department d
            WHERE s.deptno = d.deptno;

출력결과)
STUDNO   NAME     DEPTNO    DNAME         LOC
-------  ------  -------  ----------  -----
   10101    전인하      101        컴퓨터공학과    1호관
…..(이하중략)

16개의 행이 선택되었습니다.

문제 1

Student 테이블과 department 테이블을 사용하여 학생명과 학과명을 출력하세요

  select student.name, department.dname
  from student, department
  where student.deptno=department.deptno
SQL> /

 

NAME       DNAME
---------- ----------------
전인하     컴퓨터공학과
이동훈     전자공학과
박미경     컴퓨터공학과
김영균     컴퓨터공학과
박동진     전자공학과
김진영     멀티미디어학과
지은경     컴퓨터공학과
오유석     멀티미디어학과
하나리     멀티미디어학과
임유진     컴퓨터공학과
서재진     컴퓨터공학과
윤진욱     멀티미디어학과
이광훈     컴퓨터공학과
김진경     전자공학과
조명훈     전자공학과
류민정     컴퓨터공학과 

 

문제 2

Student , department , professor 테이블을 사용하여 학생이름,학과이름,지도교수이름을 아래와 같이 출력하세요.

 

select s.name, d.dname, p.name
from student s, department d, professor p
where s.deptno=d.deptno and s.profno=p.profno
SQL> /

NAME       DNAME            NAME
---------- ---------------- ----------
김진경     전자공학과       이재우
이광훈     컴퓨터공학과     성연희
전인하     컴퓨터공학과     성연희
윤진욱     멀티미디어학과   권혁일
오유석     멀티미디어학과   권혁일
김진영     멀티미디어학과   권혁일
김영균     컴퓨터공학과     이만식
류민정     컴퓨터공학과     전은지
임유진     컴퓨터공학과     전은지
지은경     컴퓨터공학과     전은지

 

3) NON-EQUI JOIN
 조인 조건에서 ‘<‘,BETWEEN a AND b 와 같이 ‘=‘ 조건이 아닌 다른 종류의 연산자를 사용하는 조인 방법이다. 많이 사용된다.

사용예)
교수 테이블과 급여 등급 테이블을 NON-EQUI JOIN 하여 교수별로 급여등급을 출력하여라.

SQL>  SELECT     p.profno ,  p.name ,  p.sal,  s.grade
          FROM        professor  p , salgrade  s
          WHERE      p.sal  BETWEEN  s.losal  AND  s.hisal;

 

문제 1

Test13 , test14 테이블을 사용하여
 1)고객별로 받을 수 있는 상품을 출력하고
 2)각 상품이 몇 개가 필요한지 출력하세요.

 

select b.cust, a.gift
from test13 a, test14 b
where b.point between a.fpoint and a.tpoint
SQL> /

CUST           GIFT
-------------- ----------------------------------------
771201-2233445 COMPUTER
620908-2121232 COMPUTER
500823-1132762 AUDIO
672102-2123452 의류교환권
650207-1765152 굴비세트
680801-1234455 갈비세트
701212-1143211 PCS무료가입권
710125-1144951 구두상품권
721109-2144952 녹차세트
721101-2144952 도서상품권

 

문제 2

Test13 , test14 테이블을 참조하여
고객의 포인트보다 낮은 모든 모든 등급의 상품을 선택할 수 있다고 할때
갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트 , 선물을 보여주는 쿼리를 작성하시오.

select a.gift, count(a.gift)
from test13 a, test14 b
where b.point between a.fpoint and a.tpoint
group by a.gift
SQL> /

GIFT                                     COUNT(S.GIFT)
---------------------------------------- -------------
COMPUTER                                         2
의류교환권                                           1
굴비세트                                              1
갈비세트                                              1
PCS무료가입권                                     1
구두상품권                                           1
AUDIO                                                 1
녹차세트                                              1
도서상품권                                           1

 

문제 3

temp 테이블의 사람 중 emp_level 테이블에서의 부장직급을 받아야 할 나이를 가지고 있는 사람의
사번,성명,생일,현재나이,현재 직급을 출력하세요.

select t.emp_id "사번", t.emp_name "이름", t.birth_date "생년월일",
to_char(round((sysdate-t.birth_date)/365,1),'99')||'   ' "나이", e.lev "직급"
from temp t, emp_level e
where e.from_age between e.from_age and e.to_age
and e.lev='부장'
SQL> /

      사번 이름       생년월일     나이   직급
---------- ---------- ------------ ------ ----------
  19970101 김길동     25-JAN-74     38    부장
  19960101 홍길동     22-MAR-73     39    부장
  19970201 박문수     15-APR-75     37    부장
  19930331 정도령     25-MAY-76     36    부장
  19950303 이순신     15-JUN-73     39    부장
  19966102 지문덕     05-JUL-72     40    부장
  19930402 강감찬     15-AUG-72     39    부장
  19960303 설까치     25-SEP-71     40    부장
  19970112 연흥부     05-NOV-76     35    부장
  19960212 배뱅이     15-DEC-72     39    부장
  20000101 이태백     25-JAN-80     32    부장
  20000102 김설악     22-MAR-80     32    부장
  20000203 최오대     15-APR-80     32    부장
  20000334 박지리     25-MAY-80     32    부장
  20000305 정북악     15-JUN-80     32    부장
  20006106 유도봉     05-JUL-80     32    부장
  20000407 윤주왕     15-AUG-80     31    부장
  20000308 강월악     25-SEP-80     31    부장
  20000119 장금강     05-NOV-80     31    부장
  20000210 나한라     15-DEC-80     31    부장


4) OUTER JOIN
NULL에 어떤 연산을 적용시켜도 결과는 null 이 된다.
따라서 조인조건중 하나의 값이 NULL일 경우 값이 NULL이 되어 출력되지 않는다.
하지만 결과가 NULL 일지라도 출력해야 할 경우가 발생하는데 이때 사용되는 것이 OUTER JOIN 이다.

 

기호를 사용한 OUTER JOIN
NULL이 출력되는 칼럼에 (+) 기호를 추가하면 된다.
예를 들어 학생 테이블과 교수 테이블을 조인할 때 지도 교수가 배정되지 않은 학생의 명단(즉, 지도교수 값이 NULL인 명단)도
출력해야 할 경우 교수 테이블에 (+) 기호를 추가한다. 하지만 (+) 기호를 양쪽 테이블에 동시에 사용할 수 없다.
 

OUTER JOIN 사용 예)
학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도 교수의 이름, 직급을 출력하여라.
단, 지도 교수가 배정되지 않은 학생이름도 함께 출력하여라.

SQL>   SELECT       s.name sname, s.grade, p.name pname, p.position
           FROM         student s, professor p
           WHERE       s.profno = p.profno(+)
           ORDER BY  p.profno;

출력결과)
SNAME    GRADE  PNAME   POSITION
------  ------  ------  --------
김진경            2    이재우    조교수
전인하            4    성연희    조교수
……..
이동훈            1
박동진            1
하나리            1
………

16개의 행이 선택되었습니다.    

 OUTER JOIN 제약 사항
 * OUTER JOIN 연산자 (+)는 NULL이 존재하는 칼럼 쪽에 표시한다. 
예) 지도교수가 없는 학생을 출력 하려 할 때는 지도 교수값이 NULL 임

 * OUTER JOIN 에서는 IN 연산자를 사용할 수 없다. 
 * 다른 조건과 OR 연산자로 결합 할 수 없다.

문제 1

학생테이블과 교수테이블을 조인하여 이름, 학년,지도교수 이름,직급을 출력하세요.
단 지도학생을 배정받지 않은 교수이름도 함께 출력하세요.

select s.name SNAME, s.grade, p.name PNAME, p.position
from student s, professor p
where s.profno(+)=p.profno;

SNAME      G PNAME      POSITION
---------- - ---------- --------------------
전인하     4 성연희     조교수
김영균     3 이만식     부교수
김진영     2 권혁일     교수
지은경     2 전은지     전임강사
오유석     4 권혁일     교수
임유진     2 전은지     전임강사
윤진욱     3 권혁일     교수
이광훈     4 성연희     조교수
김진경     2 이재우     조교수
류민정     2 전은지     전임강사
                김도훈     교수
                염일웅     전임강사
                남은혁     부교수 

 

문제 2

Temp 테이블과 emp_level 테이블을 조인하여
각 사원의 사번,이름,직급,현재연봉,해당 직급의 연봉의 상,하한금액을 보고자 한다.
단 연봉의 상,하한이 결정 안 된 수습사원은 사번,이름,직급,현재연봉만 나오면 된다.
쿼리를 만들어보세요.

select t.emp_id "사번", t.emp_name "이름", t.lev "직급", t.salary "연봉",
e.from_sal "하한금", e.to_sal "상한금"
from temp t, emp_level e
where t.lev=e.lev(+)
SQL> /

      사번 이름       직급       연봉     하한금     상한금
---------- ---------- ---- ---------- ---------- ----------
  19960303 설까치     사원   35000000   30000000   40000000
  19970112 연흥부     대리   45000000   35000000   60000000
  19950303 이순신     대리   56000000   35000000   60000000
  19960212 배뱅이     과장   39000000   37000000   75000000
  19966102 지문덕     과장   45000000   37000000   75000000
  19970201 박문수     과장   50000000   37000000   75000000
  19960101 홍길동     과장   72000000   37000000   75000000
  19930402 강감찬     차장   64000000   40000000   80000000
  19930331 정도령     차장   70000000   40000000   80000000
  19970101 김길동     부장  100000000   60000000  100000000
  20000210 나한라     수습   30000000
  20000119 장금강     수습   30000000
  20000308 강월악     수습   30000000
  20000407 윤주왕     수습   30000000
  20006106 유도봉     수습   30000000
  20000305 정북악     수습   30000000
  20000334 박지리     수습   30000000
  20000203 최오대     수습   30000000
  20000102 김설악     수습   30000000
  20000101 이태백     수습   30000000

 

WHERE 절을 사용한 SELF JOIN
 하나의 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN 을 하는 조인

사용예)
부서번호가 201 이상인 부서 이름과 해당 부서가 소속된 상위 부서의 이름을 출력하여라.

SQL>    SELECT dept.dname || ‘ is belong to ’ || org.dname
 FROM    department dept, department org
 WHERE   dept.college = org.deptno
 AND    dept.deptno >= 201;

 

문제 1

학번이 10101 인 학생의 학번, 이름, 학과이름 과 학과 위치를 출력하세요

select a.studno "학번", a.name "이름", b.dname "학과", b.loc "위치"
from student a, department b
where a.studno=10101 and a.deptno=b.deptno(+)
           검색조건            join조건

      학번 이름       학과             위치
---------- ---------- ---------------- ----------
     10101 전인하     컴퓨터공학과     1호관

문제 2

몸무게가 80kg 이상인 학생의 학번, 이름,체중,학과이름,학과위치를 출력하여라
select a.studno "학번", a.name "이름", a.weight "체중", b.dname "학과", b.loc "위치"
from student a, department b
where a.weight>=80 and a.deptno=b.deptno(+)
 ;

      학번 이름             체중 학과             위치
---------- ---------- ---------- ---------------- ----------
     10107 이광훈             92 컴퓨터공학과     1호관
     10103 김영균             88 컴퓨터공학과     1호관
     10202 오유석             92 멀티미디어학과   2호관
  

문제 3

Self join, outer join , non-equi join을 이용하여 temp 테이블을 이용하여 아래 조건의 쿼리를 작성해 보세요
Temp 테이블을 사용해 사번, 성명, 생일, 자신보다 생일이 빠른 사람수를 조회하여 자신보다 생일이 빠른 사람수 순서대로
오름차순으로 정렬해서 출력

select a.emp_id "사번", a.emp_name "성명", a.birth_date "생일",
count(b.birth_date) "빠른 사람수"
from temp a, temp b
where a.birth_date > b.birth_date(+)
group by a.emp_id, a.emp_name, a.birth_date
order by count(b.birth_date)
SQL> /

      사번 성명       생일         빠른 사람수
---------- ---------- ------------ -----------
  19960303 설까치     25-SEP-71              0
  19966102 지문덕     05-JUL-72              1
  19930402 강감찬     15-AUG-72              2
  19960212 배뱅이     15-DEC-72              3
  19960101 홍길동     22-MAR-73              4
  19950303 이순신     15-JUN-73              5
  19970101 김길동     25-JAN-74              6
  19970201 박문수     15-APR-75              7
  19930331 정도령     25-MAY-76              8
  19970112 연흥부     05-NOV-76              9
  20000101 이태백     25-JAN-80             10
  20000102 김설악     22-MAR-80             11
  20000203 최오대     15-APR-80             12
  20000334 박지리     25-MAY-80             13
  20000305 정북악     15-JUN-80             14
  20006106 유도봉     05-JUL-80             15
  20000407 윤주왕     15-AUG-80             16
  20000308 강월악     25-SEP-80             17
  20000119 장금강     05-NOV-80             18
  20000210 나한라     15-DEC-80             19