[Oracle] join
JOIN
하나의 테이블 뿐 아니라, 여러 개의 테이블에서 정보를 가져와 결과를 만들어주는 기법이다
두 개의 테이블을 join 하게 되면 각 table의 record들의 모든 조합이 출력된다
관계형 데이터베이스의 핵심 기술 중 하나이다
ANSI JOIN 방식이 있다
종류 inner join, left outer join, right outer join, full outer join, cartesian cross join
카티션 곱(cartesian product)
SELECT e.ename, d.dname FROM t_emp e CROSS JOIN t_dept d;
SELECT e.ename, d.dname FROM t_emp e, t_dept d;** 출력하는 필드에 별명을 붙일수 있듯이 테이블에도 별명을 붙여서 사용 가능하다. 복수개의 테이블에서 같은 이름의 필드가 있는 경우는 반드시 서로 다른 테이블 별명을 사용하여 접근해야 한다
Equi Join (등가 Join)
일반적으로 많이 쓰이는 Join 이며, 양쪽 테이블 Join 한 카티션곱에서 ‘같은조건’이 존재할 경우만 값을 가져오는 것
-oracle join 구문
SELECT s.name AS 학생이름,
s.deptno1 AS 학과번호, d.dname AS 학과이름
FROM t_student s
JOIN t_department d ON s.deptno1 = d.deptno; -- deptno가 같은 것만 걸러내는 걸 WHERE 절에 입력한다
-ansi 구문
SELECT s.name "학생이름", s.deptno2 "제2전공번호", d.dname "제2전공이름"
FROM t_student s, t_department d
WHERE s.deptno2 = d.deptno;
3개의 테이블 join하는 방법
-join oracle 구문
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s, t_department d, t_prodessor p
WHERE s.deptno1 = d.deptno AND s.profno = p.profno;
-ansi 구문
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM
t_student s
JOIN t_department d ON s.deptno1 = d.deptno
JOIN t_professor p ON s.profno = p.profno;
equi join (등가 join)
일반적으로 많이 쓰이는 Join 이며, 양쪽 테이블 Join 한 카티션곱에서 ‘같은조건’이 존재할 경우만 값을 가져오는 것
non-equi join (비등가 join)
서로 같은 조건 (=) 를 가진 데이터를 join 해서 가져오는 방법
그러나 크거나 작거나 하는 경우의 조건도 있을 수 있다
그러한 조건으로 join을 수행하는 것을 non-equi join이라고 한다
[실습] #6201
t_customer 테이블, t_gift 테이블을 join 하여
고객의 마일리지 포인트별로 받을수 있는 상품을 조회하여
고객의 '이름(c_name)'과 포인트(c_point) 상품명(g_name)을 출력하세요
BETWEEN ~ AND ~ 사용
-- ORACLE 구문
SELECT c.c_name "고객명", c.c_point "point", g.g_name "상품명"
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end;
-- ANSI 구문
SELECT c.c_name "고객명", c.c_point "point", g.g_name "상품명"
FROM t_customer c
JOIN t_gift g ON c.c_point BETWEEN g.g_start AND g.g_end;
-- #6202) 연습
-- 앞 예제에서 조회한 상품의 이름(gname)과 필요수량이 몇개인지 조회하세요
-- (그룹함수 동원되어야 한다)
SELECT * FROM T_CUSTOMER;
SELECT * FROM T_GIFT;
-- ORACLE 구문
SELECT g.g_name "상품명", count(*) "필요수량"
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;
-- ANSI 구문
SELECT g.g_name "상품명", count(*) "필요수량"
FROM t_customer c JOIN t_gift g
ON c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;
지금까지는 조건이 만족되는 것만 걸러져서 나오는 교집합만 했다 --> inner join
outer join
앞의 equi join, non-equi join은 join에 참여하는 모든 table에 데이터가 존재하는 경우에만 결과 값을 출력 -> inner join
outer join이란 한쪽 테이블에 데이터가 있고 한쪽 테이블에 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력한다
왼쪽 테이블을 중심으로 오른쪽의 테이블을 매치시킨다.
왼쪽 테이블의 한개의 레코드에 여러개의 오른쪽 테이블 레코드가 일치할 경우, 해당 왼쪽 레코드를 여러번 표시하게 된다.
왼쪽은 무조건 표시하고, 매치되는 레코드가 오른쪽에 없으면 NULL을 표시한다.
** 필연적으로 OUTER Join 은 DB 성능에 나쁜 영향 줌.
지도 교수가 없는 학생은 select 되지 않는다
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s INNER JOIN t_professor p
ON s.profno = p.profno;
oracle의 outer join은 ANSI 구문만 가능하다
join에 참여하지 않은 학생들도 select 되어야 한다
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s
LEFT OUTER JOIN t_professor p ON s.profno = p.profno;
학생이름과 지도교수 이름을 출력,
단! 지도 학생이 결정되지 않은 교수 명단도 출력
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s
RIGHT OUTER JOIN t_professor p ON s.profno = p.profno;
지도교수가 결정되지 않은 학생명단 모두 출력
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s
FULL OUTER JOIN t_professor p ON s.profno = p.profno;
self join
[실습] #6209
t_dept2 테이블에서
부서명 과 그 부서의 상위부서명을 출력하세요
둘 다 한 테이블에 존재한다. 그러면 동일한 테이블을 JOIN 해야 한다
어떻게? 별명을 다르게 주면 된다
SELECT * FROM t_dept2;
SELECT d1.dname "부서명", d2.dname "상위부서명"
FROM t_dept2 d1, t_dept2 d2
WHERE d1.PDEPT = d2.dcode;