서론
여태까지 기본적인 조건에 의한 데이터들을 가공하여 조회 하였다면 여러 테이블들을 합해서 결과를 만들어내는 JOIN에 대해서 알아보고자 한다.
본론
JOIN?
--JOIN이란 --여러 테이블의 데이터를 질의 할 경우 사용된다 --WHERE 절에서 조인 조건을 작성한다. --동일한 열 이름이 여러 테이블에 있는 경우 열 이름 앞에 테이블 이름이나 테이블 ALIAS를 붙인다 --N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요하다 --Cartesian Product --N개의 행을 가진 TABLE1과 M개를 가진 TABLE2의 카티시안 곱은 N*M 이다. --카티시안 곱은 다음 경우에 생성된다. --조인 조건을 생략한 경우 --조인 조건이 부적합할 경우 --첫번째 테이블의 모든행이 두번째 테이블의 모든 행에 조인되어 처리된다. --카티시안 곱이 생성되지 않도록 하려면 WHERE 절에 항상 유효한 조인 조건을 지정해야한다. --EMP 테이블에 부서번호는 있지만 부서이름을 모른다. 이 정보는 --DEPT 테이블에 있는데 이런 상황일때 조인을 통해 EMP테이블의 정보와 부서이름을 알 수 있다 --아래처럼 WHERE절에 조건을 넣지 않으면 카디션 프로덕트가 발생하여 모든 경우의 수의 데이터가 나온다. --FROM 절에는 AS 를 쓸 수가 없다. 문법 오류 남 테이블명 쓰고 한칸 띄우면 ALIAS로 인식함 --특정 테이블의 컬럼을 명시할때에는 테이블명이 아니라 ALIAS로 지정한 이름으로 해주어야 함 --오라클에서는 오라클표준의 조인구문을 사용한다. 만약 ANSI 표준을 사용하고 나서의 문제는 --오라클에서 책임을 지지 않는다 SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D; --오라클 표준 SELECT E.ENAME, D.DNAME FROM EMP E CROSS JOIN DEPT D; --ANSI 표준 --따라서 아래처럼 조건을 넣어 원하는 데이터를 얻어야 한다. SELECT EMP.*, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; -- 오라클 표준 SELECT EMP.*, DEPT.DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; --ANSI 표준
JOIN은 복수의 테이블을 가지고 WHERE 절 조건에 부합한다면 해당 행들을 하나의 행으로 표시를 해준다. 예를 들어 A테이블에는 기본키로 부서번호와 부서장 이름이 있고 B테이블에는 부서번호와 부서이름, 부서 위치등의 정보가 있다고 생각하자. 단순히 부서번호 테이블만으로는 사람이 알아먹을 수 있는 부서이름이나 부서 위치를 알 수가 없다. 따라서 A테이블의 부서번호를 가지고 B테이블에서 해당 부서번호와 동일한 행을 찾아 부서이름, 부서위치까지 나타내고자 할때 JOIN을 사용하여 나타낸다.
JOIN이 되는 과정을 두 테이블로써 상상해보자면 WHERE 절 조건에 부합한다면 조건에 맞는 A테이블의 행과 B테이블의 행이 하나의 행으로 나온다고 보면된다.
A테이블에 부서번호, 부서장이름 B테이블에 부서번호 부서이름, 부서 위치가 있다면 WHERE 절에 A.부서번호 = B.부서번호 조건일 때 조건에 맞는 데이터들이
A.부서번호, A.부서장이름, B.부서번호, B.부서이름, B.부서위치 와 같은 형태의 하나의 행으로 나온다는 것이다.
여기서 자신이 원하는 컬럼명만 SELECT 절에 명시해 주면 된다.
JOIN을 사용할 때 위에 나열된 설명이 좀 어려울 수도 있는데 JOIN을 할때 조건을 정확히 하지 않을 경우 원치 않은 결과가 나온다는 말이다. 또한 두 테이블을 조인할 경우 테이블의 두 테이블의 컬럼명이 중복 될 수도 있는데 이를 구분 하기 위해 테이블 ALIAS를 사용한다. 단 SELECT 절과는 달리 AS 를 사용 할 수 없고 한칸 띄고 ALIAS를 주는 방식이다. 두 테이블의 이름이 다르고 컬럼명들도 다르다면 ALIAS를 주지 않아도 결과가 잘 나오긴 하지만 ALIAS로 두 테이블의 별칭을 꼭 지정하고 쿼리문을 작성하는 것이 좋겠다.
EQUI JOIN, NON EQUI JOIN
--EQUI JOIN (등가 JOIN) --조인 대상 테이블에서 공통 칼럼을 =(EQUAL) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인방법 --SQL문에서 가장 많이 사용하는 조인 방법이다. --FROM절에 조인하고자 하는 테이블을 모두 명시한다. --FROM절에 명시하는 테이블은 테이블 별칭을 쓸 수 있다. --WHERE 절에 두 테이블의 공통 컬럼에 대한 조인 조건을 나열한다. --오라클 표준에서는 WHERE절에 조인조건을 넣기 때문에 AND를 넣어서 지정해준다. --ANSI표준에서는 ON으로 따로 조건을 명시 --학생 테이블(STUDENT)과 학과 테이블(DEPARTMENT)를 EQUI JOIN하여 이름, 학과번호, 소속 학과 이름을 출력하여라 SELECT S.NAME, S.DEPTNO1, D.DNAME FROM STUDENT S, DEPARTMENT D WHERE S.DEPTNO1 = D.DEPTNO ; --학생 테이블(STUDENT)과 교수 테이블(PROFESSOR)를 JOIN하여 학생이름, 지도교수번호, 지도교수 이름을 출력하여라 --S.PROFNO 에 NULL 값도 있는데 NULL이라 생략되서 나온다. --NULL인 값들도 보여주고는 싶으면 OUTER JOIN을 해야 한다.(다음 장에서 설명) SELECT S.NAME, S.PROFNO, P.NAME FROM STUDENT S, PROFESSOR P WHERE S.PROFNO = P.PROFNO; --학생 테이블(STUDENT)과 학과 테이블(DEPARTMENT) 교수 테이블(PROFESSOR)를 JOIN하여 학생의 이름과 학과이름, 지도교수 이름을 출력하세요 SELECT S.NAME, S.DEPTNO1, D.DNAME, S.PROFNO, P.NAME FROM STUDENT S, DEPARTMENT D, PROFESSOR P WHERE S.DEPTNO1 = D.DEPTNO AND S.PROFNO = P.PROFNO; --NON-EQUI JOIN (=조건이 아닌 조건을 사용할 경우) --테이블을 연결 짓는 조인 컬럼에 대한 비교조건이 '<', BETWEEN A AND B와 같이 '=' 조건이 아닌 연산자를 사용하는 경우의 조인조건 SELECT * FROM GIFT; SELECT * FROM GOGAK; --고객이 가져 갈 수 있는 최대 상품 출력 SELECT P.GNAME, P.POINT, G.GNAME FROM GOGAK P, GIFT G WHERE P.POINT BETWEEN G.G_START AND G.G_END; --고객이 가져 갈 수 있는 모든 상품 출력 SELECT P.GNAME, P.POINT, G.G_START, G.G_END, G.GNAME FROM GOGAK P, GIFT G WHERE P.POINT >= G.G_START ORDER BY P.GNAME, G.G_START; --STUDENT 테이블과 EXAM_01 테이블, HAKJUM 테이블을 조회하여 학생들의 이름과 점수와 학점을 출력하세요. SELECT * FROM STUDENT; SELECT * FROM EXAM_01; SELECT * FROM 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;
가장 기본적인 조인은 사실 CROSS JOIN이라는 것이 있다. 이것은 특별한 조건이 없이 두 테이블을 조인하는 형태이며 조건이 없기 때문에 테이블 한 행당 JOIN된 다른 테이블의 모든 행들이 이어져서 나온다. 따라서 위에서도 설명을 했었지만 테이블1 행 갯수 X 테이블2 행 갯수로 데이터가 나온다. 특별한 상황이 아닌 이상 사용할 일이 적을 것으로 보인다.
EQUI 조인은 특정 컬럼의 값이 동일 한 행들만 JOIN을 한다고 보면 된다. 만약 WHERE 절에 조건을 나열했음에도 불구하고 동일한 데이터가 없다면 아무런 데이터가 조회가 되지 않을 것이다. 동일한 데이터가 있을 때 JOIN이 되어 하나의 행으로 나타내 질 수 있고 따라서 이 상태에서 원하는 컬럼을 나열해주면 된다.
NON EQUI 조인은 "=" 조건 외에 다른 조건들일 경우를 뜻한다. 특정 값이 다른 테이블의 특정 값들의 사이에 있거나 크거나 작은 등의 조건을 사용할 때 라고 보면 된다.
위의 예제들을 잘 살펴보면 이렇게도 쓰일 수 있구나 라고 생각이 들 것이다.
실습문제
--1. EMP2 테이블과 P_GRADE 테이블을 조회하여 사원의 이름과 직급, 현재 연봉, --해당 직급의 연봉의 하한금액과 상한 금액을 출력하세요 SELECT * FROM EMP2; SELECT * FROM P_GRADE; SELECT E.NAME, E.POSITION, E.PAY, G.S_PAY, G.E_PAY FROM EMP2 E, P_GRADE G WHERE E.POSITION = G.POSITION; --NULL인 값은 자동 제외 --2. 1전공(DEPTNO1)이 101번인 학생들의 학생 이름과 지도교수 이름을 출력하세요*/ SELECT * FROM STUDENT; SELECT * FROM PROFESSOR; SELECT S.NAME, S.DEPTNO1, P.NAME FROM STUDENT S, PROFESSOR P WHERE S.DEPTNO1 = P.DEPTNO AND S.DEPTNO1 = 101; --3. GOGAK 테이블과 GIFT 테이블을 JOIN하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 --선택 할 수 있다고 할 때 산악용 자전거를 선택할 수 있는 고객명과 포인트, 상품명을 출력하세요 SELECT * FROM GOGAK; SELECT * FROM GIFT; SELECT P.GNAME, P.POINT, G.GNAME FROM GOGAK P, GIFT G WHERE P.POINT >= G.G_START AND G.GNAME = '산악용자전거'; --4. GOGAK 테이블과 GIFT 테이블을 JOIN하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 점주 입장에서는 각 상품별로 --최대 몇개의 상품이 필요한지 각 상품별 수량을 출력하세요 SELECT * FROM GOGAK ORDER BY POINT; SELECT * FROM GIFT; SELECT G.GNAME, COUNT(1) FROM GOGAK P, GIFT G WHERE P.POINT >= G.G_START GROUP BY G.GNAME; --5. EMP2 테이블과 P_GRADE 테이블을 조회하여 사원들의 이름과 나이, 현재직급, 예상직급을 출력하세요. --예상직급은 나이로 계산하며 소수점 이하는 생략하세요 SELECT * FROM EMP2; SELECT * FROM P_GRADE; SELECT E.NAME, E.BIRTHDAY, TRUNC((SYSDATE-E.BIRTHDAY)/365) + 1, E.POSITION, P.POSITION FROM EMP2 E, P_GRADE P WHERE TRUNC((SYSDATE-E.BIRTHDAY)/365) + 1 BETWEEN P.S_AGE AND P.E_AGE;
결론
EQUI JOIN, NON EQUI JOIN에 대해서 알아보았다. JOIN을 너무 어렵게 생각하지 말고 예제를 통하여 아 이럴 때 사용되는구나, 그리고 JOIN을 할 경우 어떠한 형태로 데이터들이 JOIN 되는지에 대해 상상을 자주 하다보면 금방 익힐 수 있을 것이다. 이렇게 JOIN을 하면 행들이 어떠한 형식으로 합쳐지고 이러한 상황에서 내가 나타내고자 하는 컬럼들, 그리고 더 필요하다면 각 테이블 별로의 조건들을 달아 표시를 해준다고 생각을 하면 JOIN이 어렵게 느껴지진 않을 것이다.
