JOIN

JOIN

■ Cartesian Product(카티션 곱)

Join 쿼리 중에 WHERE 절에 기술하는 Join 조건이 잘못 기술 되었거나 아예 없을 경우 발생

ex) select e.first_name, d.department_name

        from employees e, departments d;

* employees : 107  , departments : 27 개의 row가 있다면 (107x27)개의 결과를 출력하게 됨

 

■ EQUI Join

양쪽 테이블에 같은 조건의 값이 존재할 경우 해당 데이터를 가져오는 JOIN 방법

Equal 연산자(=)를 사용해서 EQUI Join 이라고 함

 

select e.first_name , d.department_name
from employees e, departments d
where e.department_id = d.department_id
  and e.department_id = 110;

 

 

* 두 테이블을  JOIN 하여 dpartment_id가 110 인 row만 출력하게 함

 

--EQUI JOIN

-- 학생 테이블(STUDENT) 과 학과테이블(DEPARTMENT) 테이블을 사용하여 학생이름, 1전공학과번호(DEPTNO1),

-- 1전공 학과 이름을 출력하세요

 

SELECT * FROM STUDENT;

SELECT * FROM DEPARTMENT; 

 

SELECT S.NAME,S.DEPTNO1,D.DNAME

FROM STUDENT S,DEPARTMENT D

WHERE S.DEPTNO1=D.DEPTNO;

 

--EQUI JOIN2

--학생 테이블(STUDENT)과 교수테이블(PROFESSOR)을 JOIN 하여 학생의 이름과

--지도교수번호, 지도교수 이름을 출력

DESC STUDENT;

DESC PROFESSOR;

 

SELECT S.NAME,P.PROFNO,P.NAME

FROM STUDENT S,PROFESSOR P

WHERE S.PROFNO = P.PROFNO;

 

--EQUI JOIN 문제2) 학생테이블(STUDENT)과 교수 테이블(PROFESSOR)을 JOIN 하여 학생의 이름과 지도교수번호, 지도교수 이름을 출력하세요

 

SELECT S.NAME AS "학생이름",S.PROFNO AS "교수번호",P.NAME AS "교수이름"

FROM STUDENT S,PROFESSOR P

WHERE S.PROFNO = P.PROFNO;

 

--EQUI JOIN 문제3) 학생테이블(STUDENT)과 학과 테이블(DEPARTMENT), 교수 테이블(PROFESSOR)을 JOIN하여

-- 학생의 이름과 학과이름, 지도교수 이름을 출력하세요.

 

SELECT S.NAME AS "학생이름",D.DNAME AS "학과이름",P.NAME AS "교수이름"

FROM STUDENT S,DEPARTMENT D,PROFESSOR P

WHERE S.PROFNO = P.PROFNO

AND S.DEPTNO1 = D.DEPTNO ;

 

--EQUI JOIN 문제4) EMP2 테이블과 P_GRADE 테이블을 조회하여 사원의 이름과 직급, 현재 연봉, 해당 직급의 연봉의

-- 하한금액과 상한 금액을 출력하세요

 

SELECT E.NAME AS "사원이름", E.POSITION AS "현재직급",

E.PAY AS "현재연봉" ,P.S_PAY AS "하한금액", P.E_PAY AS "상한금액"

FROM EMP2 E,P_GRADE P

WHERE E.POSITION=P.POSITION;

 

--EQUI JOIN 문제5) 1전공(DEPTNO1)이 101번인 학생들의 학생 이름과 지도교수 이름을 출력하세요.

 

SELECT S.NAME,P.NAME

FROM STUDENT S, PROFESSOR P

WHERE S.PROFNO = P.PROFNO

AND S.DEPTNO1 = P.DEPTNO;

 

■ NON EQUI Join

두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용 ( “=” 연산자가아닌 다른 연산자들을 이용하여 JOIN )

EX)

SELECT GO.GNAME , GO.POINT, G.GNAME

FROM GOGAK GO,GIFT G

WHERE GO.POINT BETWEEN G.G_START AND G.G_END;

 

-- NON EQUI JOIN) STUDENT 테이블과 EXAM_01 테이블 , HAKJUM 테이블을 조회하여

-- 학생들의 이름과 점수와 배점을 출력하시오

 

SELECT S.NAME, E.TOTAL , H.GRADE

FROM STUDENT S, EXAM_01 E, HAKJUM H

WHERE S.STUDNO = E.STUDNO

AND E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT;

 

-- NON EQUI JOIN) GOGAK 테이블과 GIFT 테이블을 JOIN하여 고객이 자기 포인트보다 낮은 포인트의

-- 상품 중 한가지를 선택할 수 있다고 할 때 산악용 자전거를 선택할 수 있는

-- 고객명과 포인트, 상품명을 출력하세요

 

SELECT GO.GNAME,GO.POINT,GI.GNAME

FROM GOGAK GO, GIFT GI

WHERE GO.POINT >= GI.G_START AND GI.GNAME ='산악용자전거';

 

-- NON EQUI JOIN) EMP2테이블과 P_GRADE 테이블을 조회하여 사용자들의 이름과 나이 현재 직급, 예상 직급을

-- 출력하세요. 예상 직급은 나이로 계산하여 해당 나이가 받아야 하는 직급을 의미합니다.

-- 나이는 오늘(SYSDATE)를 기준으로 하되 TRUNC로 소수점 이하는 절삭해서 계산하시오

 

SELECT E.NAME "이름", TRUNC((SYSDATE-E.BIRTHDAY)/365) "현재나이",

E.POSITION "현재직급",

G.POSITION "예상직급"

FROM EMP2 E,P_GRADE G

WHERE TRUNC((SYSDATE-E.BIRTHDAY)/365) BETWEEN G.S_AGE AND G.E_AGE;

 

■ OUTER Join

Join에 참가하는 여러 테이블에서 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에

데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법

 

OUTER JOIN 예제)  STUDENT 테이블과 PROFESSOR테이블을 JOIN 하여 학생이름과 지도교수 이름을 출력하시오

-- 단 지도학생이 결정되지 않은 교수의 명단도 함께 출력하세요

 

SELECT S.NAME, P.NAME

FROM STUDENT S, PROFESSOR P

WHERE S.PROFNO(+)=P.PROFNO;

 

--OUTER JOIN 예제 3) STUDENT 테이블과 PROFESSOR 테이블을 JOIN 하여 학생이름과 지도교수를 출력하시오

1)

SELECT S.NAME, P.NAME

FROM STUDENT S, PROFESSOR P

WHERE S.PROFNO=P.PROFNO(+)

UNION

SELECT S.NAME, P.NAME

FROM STUDENT S, PROFESSOR P

WHERE S.PROFNO(+)=P.PROFNO;

2)

SELECT S.NAME,P.NAME

FROM STUDENT S FULL OUTER JOIN PROFESSOR P

ON S.PROFNO = P.PROFNO;

 

■ SELF Join (재귀 조인)

원하는 데이터들이 하나의 테이블에 들어있을 때 사용

 

SELECT A.DNAME "부서명" , B.DNAME "상위부서명"

FROM DEPT2 A, DEPT2 B

WHERE A.PDEPT = B.DCODE;

 

-- SELF JOIN 연습문제 ) PROFESSOR 테이블에서 교수의 번호, 교수이름, 입사일, 자신보다 입사일 빠른사람 인원수를 출력

-- 단, 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하시오.

 

SELECT A.PROFNO "교수의 번호", A.NAME "교수이름", A.HIREDATE "입사일", COUNT(B.NAME) "빠른사람"//*로 하면 널 값까지

FROM PROFESSOR A, PROFESSOR B

WHERE A.HIREDATE > B.HIREDATE(+)

GROUP BY A.PROFNO, A.NAME, A.HIREDATE

ORDER BY 빠른사람;