1.정리

• 관계가 없는 테이블간의 조인시 카테시안 곱만큼의 로우가 생성된다.

• 1이란 숫자는 아무리 여러 번을 곱하여도 1이라는 수학적인 성질을 이용하여
  참조해야 할 여러 개의 테이블간의 연결을 하나의 SQL로 합칠 수가 있을 것이다.

• SQL의 별도 수행은 잦은 DBMS 호출을 초래하여 시스템 오버헤드의 주범이 된다.

• 조인은 어느 한 집합만 공집합이 되더라도 전체가 공집합이 되어 버리므로
  다른 성공한 집합까지 실패하게 된다.

• SQL에서 OUTER 조인을 하고자 하는 임의의 조인 컬럼에 반드시 성공하는 집합의 컬럼을
  결과에 영향을 미치지 않도록 가공하여 추가하면 어느 한 집합이 공집합이 되더라도
  수행결과는 정상적으로 추출된다.

2.테이블 설계

(1)고객(CUSTOMER)
고객번호(CUST_NO/PK), 고객명(CUST_NAME)

(2)사원(EMPLOYEE)
사원번호(EMP_NO/PK), 부서(DEPT), 호봉(SALEGRADE)

(3)예산(BUDGET)
부서(DEPT_NO/PK1), 계정과목(ACCOUNT/PK2), 예산년도(BUDGET_YR/PK3), 편성금액(BUDGET_AMT)

3.CREATE TABLE

CREATE  TABLE ORA9.CUSTOMER  (

COMMENT ON TABLE  ORA9.CUSTOMER IS '고객';

COMMENT ON COLUMN ORA9.CUSTOMER.CUST_NO IS '고객번호';

COMMENT ON COLUMN ORA9.CUSTOMER.CUST_NAME IS '고객명';

ALTER TABLE  ORA9.CUSTOMER ADD (

CREATE  TABLE ORA9.EMPLOYEE (

COMMENT ON TABLE  ORA9.EMPLOYEE IS '사원';

COMMENT ON COLUMN ORA9.EMPLOYEE.EMP_NO IS '사원번호';

COMMENT ON COLUMN ORA9.EMPLOYEE.DEPT IS '부서';

COMMENT ON COLUMN ORA9.EMPLOYEE.SALEGRADE IS '호봉';

ALTER TABLE  ORA9.EMPLOYEE ADD (

CREATE  TABLE ORA9.BUDGET (

COMMENT ON TABLE  ORA9.BUDGET IS '예산';

COMMENT ON COLUMN ORA9.BUDGET.DEPT_NO IS '부서';

COMMENT ON COLUMN ORA9.BUDGET.ACCOUNT IS '계정과목';

COMMENT ON COLUMN ORA9.BUDGET.BUDGET_YR IS '예산년도';

COMMENT ON COLUMN ORA9.BUDGET.BUDGET_AMT IS '편성금액';

ALTER TABLE  ORA9.BUDGET ADD (

4.INSERT

INSERT INTO ORA9.CUSTOMER VALUES('0001','고객1');

INSERT INTO ORA9.CUSTOMER VALUES('0002','고객2');

INSERT INTO ORA9.CUSTOMER VALUES('0003','고객3');

INSERT INTO ORA9.CUSTOMER VALUES('0004','고객4');

INSERT INTO ORA9.CUSTOMER VALUES('0005','고객5');

COMMIT;

INSERT INTO ORA9.EMPLOYEE VALUES('0001','개발','1');

INSERT INTO ORA9.EMPLOYEE VALUES('0002','영업','2');

INSERT INTO ORA9.EMPLOYEE VALUES('0003','마케팅','1');

INSERT INTO ORA9.EMPLOYEE VALUES('0004','경영','3');

INSERT INTO ORA9.EMPLOYEE VALUES('0005','지원','1');

COMMIT;

INSERT INTO ORA9.BUDGET VALUES('0001','1101','2009',3000000);

INSERT INTO ORA9.BUDGET VALUES('0001','1101','2008',2500000);

INSERT INTO ORA9.BUDGET VALUES('0001','1102','2009',3500000);

INSERT INTO ORA9.BUDGET VALUES('0001','1102','2008',2800000);

INSERT INTO ORA9.BUDGET VALUES('0002','1101','2009',2500000);

INSERT INTO ORA9.BUDGET VALUES('0002','1101','2008',2000000);

INSERT INTO ORA9.BUDGET VALUES('0002','1102','2009',4500000);

INSERT INTO ORA9.BUDGET VALUES('0002','1102','2008',3600000);

INSERT INTO ORA9.BUDGET VALUES('0003','1101','2009',3200000);

INSERT INTO ORA9.BUDGET VALUES('0003','1101','2008',2400000);

INSERT INTO ORA9.BUDGET VALUES('0003','1102','2009',3300000);

INSERT INTO ORA9.BUDGET VALUES('0003','1102','2008',2700000);

INSERT INTO ORA9.BUDGET VALUES('0004','1101','2009',4400000);

INSERT INTO ORA9.BUDGET VALUES('0004','1101','2008',3800000);

INSERT INTO ORA9.BUDGET VALUES('0004','1102','2009',2500000);

INSERT INTO ORA9.BUDGET VALUES('0004','1102','2008',1800000);

INSERT INTO ORA9.BUDGET VALUES('0005','1101','2009',3300000);

INSERT INTO ORA9.BUDGET VALUES('0005','1101','2008',2900000);

INSERT INTO ORA9.BUDGET VALUES('0005','1102','2009',3700000);

INSERT INTO ORA9.BUDGET VALUES('0005','1102','2008',3000000);

COMMIT;

5.결과

(1)SQL 분리시 

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO1
  2
  3   (P_CUST_NO  ORA9.CUSTOMER.CUST_NO%TYPE,
  4    P_EMP_NO  ORA9.EMPLOYEE.EMP_NO%TYPE,
  5    P_DEPT_NO  ORA9.BUDGET.DEPT_NO%TYPE
  6   )
  7
  8  IS
  9
 10       CURSOR CUSTOMER_CSR IS
 11         SELECT CUST_NAME
 12           FROM ORA9.CUSTOMER
 13          WHERE CUST_NO = P_CUST_NO;
 14
 15       CURSOR EMPLOYEE_CSR IS
 16         SELECT DEPT
 17           FROM ORA9.EMPLOYEE
 18          WHERE EMP_NO = P_EMP_NO;
 19
 20       CURSOR BUDGET_CSR IS
 21         SELECT BUDGET_AMT
 22           FROM ORA9.BUDGET
 23          WHERE DEPT_NO = P_DEPT_NO
 24           AND  ACCOUNT = '1101'
 25           AND BUDGET_YR = TO_CHAR(SYSDATE, 'YYYY');
 26
 27     C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
 28
 29     C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
 30
 31     C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
 32
 33  BEGIN
 34
 35     OPEN CUSTOMER_CSR;
 36
 37        LOOP
 38
 39           FETCH CUSTOMER_CSR INTO C_CUST_NM;
 40
 41           EXIT  WHEN CUSTOMER_CSR%NOTFOUND;
 42
 43           DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
 44
 45              OPEN EMPLOYEE_CSR;
 46
 47                 LOOP
 48
 49                    FETCH EMPLOYEE_CSR INTO C_DEPT;
 50
 51                    EXIT  WHEN EMPLOYEE_CSR%NOTFOUND;
 52
 53                    DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
 54
 55                       OPEN BUDGET_CSR;
 56
 57                          LOOP
 58
 59                             FETCH BUDGET_CSR INTO C_BUDGET_AMT;
 60
 61                             EXIT  WHEN BUDGET_CSR%NOTFOUND;
 62
 63                             DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
 64
 65                          END LOOP;
 66
 67                       CLOSE BUDGET_CSR;
 68
 69                 END LOOP;
 70
 71              CLOSE EMPLOYEE_CSR;
 72
 73        END LOOP;
 74
 75     CLOSE CUSTOMER_CSR;
 76
 77  EXCEPTION
 78
 79     WHEN OTHERS THEN
 80
 81     DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
 82
 83  END;
 84  /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO1('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

(2)SQL 통합시

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO2
  2
  3   (P_CUST_NO  ORA9.CUSTOMER.CUST_NO%TYPE,
  4    P_EMP_NO  ORA9.EMPLOYEE.EMP_NO%TYPE,
  5    P_DEPT_NO  ORA9.BUDGET.DEPT_NO%TYPE
  6   )
  7
  8  IS
  9
 10       CURSOR NOREL_JOIN_CSR IS
 11         SELECT CUST_NAME
 12                       ,DEPT
 13                       ,BUDGET_AMT
 14           FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
 15        WHERE CUST_NO = P_CUST_NO
 16              AND EMP_NO = P_EMP_NO
 17              AND DEPT_NO = P_DEPT_NO
 18              AND ACCOUNT = '1101'
 19              AND BUDGET_YR = TO_CHAR(SYSDATE, 'YYYY');
 20
 21     C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
 22
 23     C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
 24
 25     C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
 26
 27  BEGIN
 28
 29     OPEN NOREL_JOIN_CSR;
 30
 31        LOOP
 32
 33           FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
 34
 35           EXIT  WHEN NOREL_JOIN_CSR%NOTFOUND;
 36
 37           DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
 38
 39           DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
 40
 41           DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
 42
 43        END LOOP;
 44
 45     CLOSE NOREL_JOIN_CSR;
 46
 47  EXCEPTION
 48
 49     WHEN OTHERS THEN
 50
 51     DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
 52
 53  END;
 54  /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO2('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

(3)OUTER JOIN으로 SQL 통합시(실패)

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO3
  2
  3   (P_CUST_NO  ORA9.CUSTOMER.CUST_NO%TYPE,
  4    P_EMP_NO  ORA9.EMPLOYEE.EMP_NO%TYPE,
  5    P_DEPT_NO  ORA9.BUDGET.DEPT_NO%TYPE
  6   )
  7
  8  IS
  9
 10       CURSOR NOREL_JOIN_CSR IS
 11         SELECT CUST_NAME
 12                      ,DEPT
 13                      ,BUDGET_AMT
 14           FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
 15        WHERE CUST_NO = P_CUST_NO
 16             AND EMP_NO = P_EMP_NO
 17             AND DEPT_NO(+) = P_DEPT_NO
 18             AND ACCOUNT(+) = '1100'
 19             AND BUDGET_YR(+) = TO_CHAR(SYSDATE, 'YYYY');
 20
 21     C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
 22
 23     C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
 24
 25     C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
 26
 27  BEGIN
 28
 29     OPEN NOREL_JOIN_CSR;
 30
 31        LOOP
 32
 33           FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
 34
 35           EXIT  WHEN NOREL_JOIN_CSR%NOTFOUND;
 36
 37           DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
 38
 39           DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
 40
 41           DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
 42
 43        END LOOP;
 44
 45     CLOSE NOREL_JOIN_CSR;
 46
 47  EXCEPTION
 48
 49     WHEN OTHERS THEN
 50
 51     DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
 52
 53  END;
 54  /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO3('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.  \--> 결과값이 출력되지 않았음

(4)OUTER JOIN으로 SQL 통합시(성공)

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO4
  2
  3   (P_CUST_NO  ORA9.CUSTOMER.CUST_NO%TYPE,
  4    P_EMP_NO  ORA9.EMPLOYEE.EMP_NO%TYPE,
  5    P_DEPT_NO  ORA9.BUDGET.DEPT_NO%TYPE
  6   )
  7
  8  IS
  9
 10       CURSOR NOREL_JOIN_CSR IS
 11         SELECT CUST_NAME
 12                      ,DEPT
 13                      ,BUDGET_AMT
 14           FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
 15        WHERE CUST_NO = P_CUST_NO
 16             AND EMP_NO = P_EMP_NO
 17             AND DEPT_NO(+) = P_DEPT_NO || SUBSTR(EMP_NO,0,0)
 18             AND ACCOUNT(+) = '1100'
 19             AND BUDGET_YR(+) = TO_CHAR(SYSDATE, 'YYYY');
 20
 21     C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
 22
 23     C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
 24
 25     C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
 26
 27  BEGIN
 28
 29     OPEN NOREL_JOIN_CSR;
 30
 31        LOOP
 32
 33           FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
 34
 35           EXIT  WHEN NOREL_JOIN_CSR%NOTFOUND;
 36
 37           DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
 38
 39           DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
 40
 41           DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
 42
 43        END LOOP;
 44
 45     CLOSE NOREL_JOIN_CSR;
 46
 47  EXCEPTION
 48
 49     WHEN OTHERS THEN
 50
 51     DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
 52
 53  END;
 54  /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO4('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

6. 주석

NOREL_JOIN_PRO3의 경우는 만족하는 값이 없으면 OUTER-JOIN 을 했다 하더라도 항상 공집합이다.

즉, DEPT_NO(+) = P_DEPT_NO 는 DEPT_NO = P_DEPT_NO 와 같은 결과를 낸다.

OUTER-JOIN 은 반드시 성공한 어떤 집합과 연결을 할 때만 의미가 있다.

즉, 상수값과는 OUTER-JOIN을 해도 결과는 달라지지 않는다.

반면 NOREL_JOIN_PRO4 에서는 

CUST_NO = P_CUST_NO, EMP_NO = P_EMP_NO 를 만족하는 집합이 BUDGET 테이블과 OUTER-JOIN을 시도하는데,

DEPT_NO(+) = P_DEPT_NO || SUBSTR(EMP_NO,0,0) 조건을 보면

SUBSTR(EMP_NO,0,0)은 NULL 값이 되므로 P_DEPT_NO에 붙이더라도 그 결과에는 영향을 미치지 않는다.

이 구문의 추가로 인해 EMPLOYEE 테이블의 수행 결과는 BUDGET 테이블의 수행 결과에 영향을 받지 않는다.