201026 외부조인(OUTER JOIN)

90%이상을 동등조인을 쓰지만 OUTER JOIN도 알고 있어야함

아까 부서코드가 한쪽에 없으면 무시했는데, 무시하지않는조인이 외부조인이다.

 

4. 외부조인 (OUTER JOIN)

- 조인에 참여하는 테이블에 자료의 종류(갯수가 아님)가 부족한 경우 (조인조건을 만족하지 않은 경우) 내부조인은 결과에 포함시키지 않지만

- 외부조인은 부족한 테이블에 NULL행을 삽입하여 양쪽 테이블의 행개수를 일치시키고 조인을 수행

- '모든', '전부'와 같은 수식어가 사용된 경우

- SELECT절에 사용되는 컬럼은 자료가 많은 테이블의 컬럼을 사용 (양쪽 모두에 똑같은 컬럼이 존재할때)

  *사원테이블이 107행으로 부서테이블(27)보다 훨 많지만 부서종류로 치면 부서테이블이 27가지 사원은 12개임 더많은쪽이 부서테이블임 이때 사원테이블은 16개가  NULL로 찍힌다.

- COUNT함수 사용시 '*' 대신 컬럼명 기술

 

 

(일반 OUTER JOIN 사용형식)

 - '(+)'연산자를 부족한 테이블에 속한 컬럼명에 추가 (조인조건 기술시)

- 조인조건이 1개이상 있고 모두 외부조인이 수행되어야 하면 모든 조인조건에 '(+)'을 추가해야 함

※ 주의!
한번에 한 테이블만 외부조인을 할 수 있다. 예를 들어 A,B,C 테이블에서 A를 기준으로 B테이블과 외부조인을 한 경우(A=B(+)) 동시에 C를 기준으로 B를 외부조인(C=B(+))할 수 없다.

- 일반조건이 같이 부여된 경우 부정확한 결과 산출(서브쿼리나, ANSI외부조인을 사용해야 함)

 

(ANSI OUTER JOIN 사용형식)

SELECT 컬럼LIST,...
FROM 테이블명1 (별칭)
RIGHT|LEFT|FULL [OUTER] JOIN 테이블명2 [별칭] ON(조인조건 [AND 일반조건])
             :
[WHERE (모든테이블에 관련되어진)일반조건];

- RIGHT|LEFT: 테이블명1의 자료 종류가 테이블명2의 자료 종류보다 많으면 LEFT (많은쪽이 왼쪽), 그 반대이면 RIGHT

- FULL : 양 쪽 모든 테이블이 모두 부족한 경우

EX

부서테이블의 모든 부서별 인원수를 사원테이블을 이용하여 조회하시오.

부서코드 NULL은 무시함

Alias는 부서코드, 부서명, 인원수

* 내부조인을 썼을 때

SELECT  A.DEPARTMENT_ID 부서코드, 
        A.DEPARTMENT_NAME 부서명, 
        COUNT(*) 인원수
FROM    DEPARTMENTS A, EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID, A.DEPARTMENT_NAME
ORDER BY 3;

NULL 은 생략되었다 (내부조인이기떄문에 부서에 사원이없으면 다 생략됨)

(일반 외부조인)

부족한 쪽에 NULL을 채우라했으니까 1이 찍히는거임

(ANSI 외부조인)

SELECT  B.DEPARTMENT_ID 부서코드, 
        B.DEPARTMENT_NAME 부서명, 
        COUNT(EMP_NAME) 인원수
FROM    EMPLOYEES A 
RIGHT OUTER JOIN DEPARTMENTS B ON (A.DEPARTMENT_ID=B.DEPARTMENT_ID)
GROUP BY    B.DEPARTMENT_ID , 
            B.DEPARTMENT_NAME
ORDER BY 1;

근데 얘는 정확하게 말하면 왼쪽오른쪽이 다 부족하기 때문에 FULL 을 해보자

RIGHT대신 FULL을 넣어봤을 때

 

예

장바구니 테이블에서 2005년 6월 모든 회원별 구매현황을 조회하시오

Alias는 회원번호, 회원명, 구매액

모든 (OUTER) 기간(이 오면 보통 일반조건) ~별 (그룹바이)

 

(내부조인으로 해보자)

SELECT  B. MEM_ID 회원번호, 
        B. MEM_NAME 회원명, 
        SUM(A.CART_QTY*C.PROD_PRICE) 구매액
FROM CART A, MEMBER B, PROD C
WHERE   A.CART_MEMBER=B.MEM_ID 
        AND A.CART_PROD=C.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) LIKE '200506'
GROUP BY B. MEM_ID ,B. MEM_NAME 
ORDER BY 1;

 

(일반 외부조인을 해보자)

SELECT  B. MEM_ID 회원번호, 
        B. MEM_NAME 회원명, 
        SUM(A.CART_QTY*C.PROD_PRICE) 구매액
FROM CART A, MEMBER B, PROD C
WHERE   A.CART_MEMBER(+)=B.MEM_ID 
        AND A.CART_PROD(+)=C.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) LIKE '200506'
GROUP BY B. MEM_ID ,B. MEM_NAME 
ORDER BY 1;

만야 이렇게 하면 멤버테이블에 맞춰서 카트확장(+)했는데 또 밑줄에서 PROD에 맞춰 CART(+)해서 오류남

SELECT  B. MEM_ID 회원번호, 
        B. MEM_NAME 회원명, 
        SUM(A.CART_QTY*C.PROD_PRICE) 구매액
FROM CART A, MEMBER B, PROD C
WHERE   A.CART_MEMBER(+)=B.MEM_ID 
        AND A.CART_PROD=C.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) LIKE '200506'
GROUP BY B. MEM_ID ,B. MEM_NAME 
ORDER BY 1;

이렇게 해도 6명밖에 안나옴

>> 외부조인 결과를 얻을 수 없음

해결법 : ANSI 외부조인을 이용하는 법

PROD는 A와 B가 조인한 결과!!와 조인하기때문에 CAR와 MEM조인은 회원의 종류는 다들어가는데 상품은 안들어가있음 (그 사람들이 산 상품의 종류밖에없음) PROD는 모든상품의 정보가 다 들어가있음. 그래서 RIGHT임

SELECT  B.MEM_ID 회원번호, 
        B. MEM_NAME 회원명, 
        SUM(A.CART_QTY*C.PROD_PRICE) 구매액
FROM    PROD C
    LEFT OUTER JOIN CART A ON (A.CART_PROD=C.PROD_ID
            AND    A.CART_NO LIKE '200506%')
    RIGHT OUTER JOIN MEMBER B ON (A.CART_MEMBER=B.MEM_ID)
GROUP BY B.MEM_ID, B.MEM_NAME
ORDER BY 1;

**''회원별'인걸 명심하자

SELECT  B.MEM_ID 회원번호, 
        B. MEM_NAME 회원명, 
        SUM(A.CART_QTY*C.PROD_PRICE) 구매액
FROM    PROD C
    LEFT OUTER JOIN CART A ON (A.CART_PROD=C.PROD_ID)
    RIGHT OUTER JOIN MEMBER B ON (A.CART_MEMBER=B.MEM_ID)
WHERE A.CART_NO LIKE '200506%'
GROUP BY B.MEM_ID, B.MEM_NAME
ORDER BY 1;

WHERE절은 밖으로 빼면 조인이 전부 처리되어진 다음에 WHERE절이 처리되기 때문에 6개만 출력이된다.

6개 밖에 안나옴 WHERE절이 맨 마지막에 처리됨

 

해결법: 서브쿼리 이용하는 방법

(SUBQURERY 를 사용)

 

SELECT  B.MEM_ID 회원번호, 
        B.MEM_NAME 회원명, 
        D.OAMT 구매액
FROM    MEMBER B, 
        (SELECT A.CART_MEMBER MID,
                SUM(A.CART_QTY*C.PROD_PRICE) OAMT
        FROM CART A, PROD C
        WHERE A.CART_PROD=C.PROD_ID
        AND A.CART_NO LIKE '200506%'
        GROUP BY A.CART_MEMBER) D
WHERE   D.MID(+)=B.MEM_ID
ORDER BY 1;

저기 FROM절 안의 서브쿼리의 특징은 (INLINE 서브쿼리)라고 하는데,

얘는 단독으로 실행시켜도 결과가 나와야함

SELECT A.CART_MEMBER MID,
                SUM(A.CART_QTY*C.PROD_PRICE) OAMT
        FROM CART A, PROD C
        WHERE A.CART_PROD=C.PROD_ID
        AND A.CART_NO LIKE '200506%'
        GROUP BY A.CART_MEMBER

 

문제) 2005년 1~6월 모든 거래처별 매입금액을 조회하시오

Alias 는 거래처코드, 거래처명, 매입금액 이다.

 

1. 서브쿼리

01

2. 안시

 

3. 일반조인