Grizzly`s Cave: JOIN 과 집합 (UNION, INTERSECT, MINUS)

CROSS JOIN : 두 테이블의 가능한 경우로 JOIN 한다. 데카르트 곱이 DB에 적용된 Catecian Product이다.

A⊙B = { (1,a), (1,b),(1,c),(2,a), (2,b),(2,c),(3,a), (3,b),(3,c),(4,a), (4,b),(4,c) }

SELECT * FROM EMP, DEPT; -- SQL 1992 code
SELECT * FROM JOIN EMP,DEPT ON .. -- SQL 1999 CODE
SELECT * FROM EMP CROSS JOIN DEPT;

CROSS JOIN을 사용하는 쿼리문을 살펴보자. EMP 테이블로부터 사원명, 연봉, 전체평균연봉, 연봉-전체평균을 구하는 쿼리이다. 우선 전체평균연봉을 구한다.

SELECT ROUND( AVG(SAL*12+ NVL(COMM,0))) "YPAY"
FROM EMP;

위에서 구한 값을 가지고 사원의 연봉값과 연산하자.

SELECT ENAME "사원명", SAL*12+NVL(COMM,0) "연봉" ,YPAY "평균연봉",SAL*12+NVL(COMM,0)-"YPAY" "차액"
FROM EMP
CROSS JOIN
(SELECT ROUND( AVG(SAL*12+ NVL(COMM,0)),1) "YPAY"
FROM EMP );

CROSS JOIN은 모든 값에 대한 모든 경우를 JOIN하므로, 결과적으로 각 RECORD에 붙이는 것과 같은 결과를 가져온다.

INNER JOIN : 일반적인 JOIN문으로 생각하면 쉽다. 두 테이블 간에 지정된 COLUMN의 공통되는 값을 기준으로 나머지 COLUMN들의 값을 붙인다.

SELECT * FROM EMP;

사원 이름당 급여와, 직종별 평균급여, 평균급여와의 차이를 구해보자.

SELECT  E.JOB "직종",  ENAME "사원명", SAL "급여", M."평균" "직종평균급여",
(SAL - M."평균")"급여-평균급여"
FROM EMP E
JOIN ( SELECT JOB, ROUND(AVG(SAL),1) "평균"
           FROM EMP
           GROUP BY JOB ) M
ON E.JOB = M.JOB
ORDER BY E.JOB ;

EMP 테이블을 중복하여 JOIN하게 되는데 한 테이블로 부터는 직업당 평균 직업을 가져오고 다른 테이블로부터는 직업과 이름, 연봉을 가져온다. 그리고 두 테이블을 직업을 기준으로 JOIN하게 되면 해당 값을 기준으로 직업당 평균 연봉이 나타나게 되고, 이를 연산하면 차이를 가져오게 된다.

OUTER JOIN : LEFT와 RIGHT의 방향성을 가지고 있으며, 양방향성을 가지는 FULL도 가능하다. 방향성에 의지하여 한 테이블에 대한 다른 테이블의 NULL을 포함한 모든 DATA를 가져온다.

SELECT * FROM DEPT;

SELECT DECODE(GROUPING(DNAME),1,'총계', NVL(TO_CHAR(DNAME),'인턴')) "부서명" ,LOC "위치", COUNT (*) "인원수"
FROM EMP E
LEFT JOIN (
SELECT *
FROM DEPT) D
ON E.DEPTNO = D.DEPTNO
GROUP BY GROUPING SETS ((DNAME,LOC), ())
ORDER BY 1;

부서번호에 대한 정보를 가지고 있는 DEPT 테이블을 EMP 테이블을 가르키며 JOIN 하여 그에 해당하는 값인 부서명과 위치를 가져오고, EMP 테이블부터 그룹함소룰 이용하여 인원수를 가져왔다. 이는 EMP 테이블에 방향성을 가지므로, JOIN의 왼쪽에 있는 EMP테이블의 값은 전부 가져오나, 존재하지 않는 인턴에 대한 값은 가져올수 없으므로 위치등의 정보가 NULL 값이게 된다. 이제 RIGHT 와 FULL에 따른 결과를 비교해 보자

SELECT DECODE(GROUPING(DNAME),1,'총계', NVL(TO_CHAR(DNAME),'인턴')) "부서명" ,LOC "위치", COUNT (*) "인원수"
FROM EMP E
RIGHT JOIN (
SELECT *
FROM DEPT) D
ON E.DEPTNO = D.DEPTNO
GROUP BY GROUPING SETS ((DNAME,LOC), ())
ORDER BY 1;

RIGHT JOIN의 결과는 LEFT JOIN의 결과와 비슷해 보이나, 총계를 확인하면 그 값이 다름을 알 수 있다. LEFT JOIN에서는 EMP 테이블을 기준으로 하여 17의 값이 나왔지만 RIGHT JOIN의 경우에는 LEFT JOIN에서 없었던 OPERRATION이 추가되었고, DEPTNO가 없는 인턴이 삭제가 되었음을 알 수 있다.

SELECT DECODE(GROUPING(DNAME),1,'총계', NVL(TO_CHAR(DNAME),'인턴')) "부서명" ,LOC "위치", COUNT (*) "인원수"
FROM EMP E
FULL JOIN (
SELECT *
FROM DEPT) D
ON E.DEPTNO = D.DEPTNO
GROUP BY GROUPING SETS ((DNAME,LOC), ())
ORDER BY 1;

위와 같이 FULL JOIN을 할 경우에는 양측의 존재하는 값을 모두 JOIN하게 되고, 그 결과로 양측의 NULL값과 일치 되지 않는 값들이 모두 JOIN되어 보여진다.

SELF JOIN : 스스로를 JOIN하는 것을 말한다. 테이블의 값이 자기 스스로 참조되어 사용될 때에 사용 된다.

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME
FROM EMP E1 JOIN EMP E2
ON E1.MGR = E2.EMPNO;

위의 쿼리문은 EMP 테이블이 스스로 JOIN하여 매니저의 이름을 가져오게 된다.
다음은 매니저가 존재하지 않는 경우를 포함하여 모든 관계를 나타내는 쿼리문이다.

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME
FROM EMP E1 LEFT JOIN EMP E2
ON E1.MGR = E2.EMPNO;

이는 1999년을 기준으로 한 SQL문이고 기존의 1992년의 양식으로 다음과 같이 표현이 되니 알아 놓도록 하자. 기존에 구축한 DB의 경우, 종종 볼 수 있는 구조이다.

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO(+);

1992년 방식으로는 JOIN임을 별도로 명시하지 않고 ','로 표시하며 ON 대신 조건절인 WHERE절에서 JOIN 조건을 명시한다. 또 (+)가 없는 쪽을 전부 보여주고 그에 맞춰 반대쪽을 보이는 방식으로 OUTER JOIN을 나타냈다. 위의 쿼리문의 경우는 WHERE 절 우측의 조건에 (+)가 붙었으므로 LEFT OUTER JOIN이 된다.

책의 저자와 책 이름, 그 책의 로열티를 나타내는 테이블을 만들어 보자

CREATE TABLE BOOKAUTHOR (
AUTHERORID VARCHAR2(10),
BOOKCODE VARCHAR2(20),
LOYALTY NUMBER(5)
) TABLESPACE USERS;

INSERT INTO BOOKAUTHOR VALUES('LEEJH','알기쉬운 오라클',500);
INSERT INTO BOOKAUTHOR VALUES('PAKSE','재미난 음악세계',300);
INSERT INTO BOOKAUTHOR VALUES('LIMKM','미술의 감동',100);
INSERT INTO BOOKAUTHOR VALUES('LIMKM','재미난 음악세계',200);
INSERT INTO BOOKAUTHOR VALUES('KANGJK','미술의 감동',150);
INSERT INTO BOOKAUTHOR VALUES('KIMHW','오국지',800);
COMMIT;

SELECT * FROM BOOKAUTHOR;

이중 공저로 지어진 책에 대한 정보를 가져와 보자. 이는 서브쿼리를 이용하여 처리할 수 있다.

WITH T AS (
SELECT
DISTINCT( CASE WHEN COUNT(BOOKCODE) >1 THEN BOOKCODE END) "BOOKCODE"
FROM BOOKAUTHOR
GROUP BY BOOKCODE)

SELECT AUTHERORID, T.BOOKCODE, LOYALTY
FROM BOOKAUTHOR B
JOIN T
ON B.BOOKCODE = T.BOOKCODE;

우선 BOOKCODE에 대한 COUNT가 2이상인 경우가 공저가 있다는 것을 알 수 있다. 그러므로 서브쿼리로 COUNT가 2이상인 것들의 BOOKCODE를 얻어오고, 이를 기존의 테이블의 BOOKCODE와 비교하여 일치하는 것에 대한 정보만 가져오면 된다.

또는 다음과 같이 CROSS JOIN을 이용하여서도 처리가 가능하다.

SELECT E1.AUTHERORID,E1.BOOKCODE,E1.LOYALTY
FROM BOOKAUTHOR E1
CROSS JOIN BOOKAUTHOR E2
WHERE E1.BOOKCODE = E2.BOOKCODE AND E1.AUTHERORID <> E2.AUTHERORID;

CROSS JOIN의 경우 모든 경우의 값에 대하여 JOIN을 하게 되고, 그렇게 된 값들 중, BOOKCODE에 따른AUTHERORID가 같지 않은 경우가 있다면, BOOKCODE에 따른 AUTHERORID 값이 1개가 아니란 것이므로 그 경우의 값을 가져오면 되는 것이다.
얼핏 보면 JOIN이 어렵기는 하지만, 조인이 서브쿼리보다 성능이 좋기 때문에 대용량 DATA의 경우에는 JOIN을 사용 하는 것이 좋다.

NON EQUI JOIN : JOIN의 조건식 ON의 비교값이 EQUAL (=)이 아닌 JOIN이다. 범위값등을 기준으로 JOIN할 때 쓰인다. MS-SQL의 경우 2005 버전까진 없었다.

SELECT * FROM SALGRADE;

SELECT ENAME, SAL FROM EMP;

위의 두 테이블을 이용하여 SAL에 대한 등급을 JOIN하여 보자.

SELECT ENAME, SAL, GRADE
FROM EMP
JOIN SALGRADE
ON SAL >= LOSAL AND SAL <= HISAL;

또는 다음과 같이 BETWEEN을 이용하여서도 사용할 수 있다.

SELECT ENAME, SAL, GRADE
FROM EMP
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL;

일정 범위 값에 대한 처리 경우, CASE문을 이용하여서도 처리가 가능하다. 하지만 DATA의 양이 많거나 GRADE가 수시로 변할 경우에는 처리량이 많아지게 되고, PROCEDURE 등으로 처리 했을 경우에는 해당 PROCEDURE마다 . 하지만 위와 같이 GRADE에 대한 별도의 테이블을 구축하여 둘 경우, PROCEDURE는 수정 하지 않고, 영역 값만을 손보면 되므로 상대적으로 처리량이 적다.

집합 함수 : 두 테이블간의 결과를 집합처럼 처리하여 준다.
UNION :
UNION ALL :
MINUS :
INTERSECT :

CREATE TABLE TBL_JUMUN (
JUNO NUMBER,
JECODE VARCHAR2(20),
JUDAY DATE DEFAULT SYSDATE,
JUSU NUMBER
)TABLESPACE USERS;

CREATE SEQUENCE JUMUN_SEQ -- MS-SQL : IDENTITY
START WITH 1 -- 초기
INCREMENT BY 1 -- 증가
NOMAXVALUE -- 제한
NOCYCLE; -- 순환
-- CYCLE 시
-- MINVALUE 설정

INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'SWK',TO_DATE('2009-05-24 09:05:02','YYYY-MM-DD HH24:MI:SS'),20);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'KJK',TO_DATE('2009-05-24 09:30:01','YYYY-MM-DD HH24:MI:SS'),15);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'SWK',TO_DATE('2009-05-24 10:05:01','YYYY-MM-DD HH24:MI:SS'),25);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'KKK',TO_DATE('2009-05-24 11:20:50','YYYY-MM-DD HH24:MI:SS'),5);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'KJK',TO_DATE('2009-05-24 13:20:50','YYYY-MM-DD HH24:MI:SS'),5);

INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'SWK',DEFAULT,40);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'KJK',DEFAULT,21);
INSERT INTO TBL_JUMUN VALUES (JUMUN_SEQ.NEXTVAL , 'YPR',DEFAULT,10);

SELECT * FROM TBL_JUMUN

-- 테이블이 커질 때, 단 1개의 값을 가져오기 위해서도 전체를 메모리에 올려야 하는 !!
-- 빈도가 낮은 것들은 정리하여 분리 후, 필요 시 결합하여 사용 - UNION
-- JOIN은 COLUMN 과 COLUMN을 붙이는것
-- UNION은 ROW를 합치는 것!!

SELECT *
FROM TBL_JUMUN
WHERE TO_CHAR(JUDAY,'YYYY-MM-DD') < TO_CHAR(SYSDATE,'YYYY-MM-DD');