外部結合

内部署名は、署名条件を満たさない資料

を無視する.

外部結合データ型がないテーブルにNULL行を追加し、結合演算

を実行する.

結合条件で、欠落したエッジの列名の後に外部結合演算子「(+)」

を追加します.
1つ以上の条件が

結合である場合、すべての条件は外部結合演算子

を使用する必要があります.

同時に、1つのテーブルが2つ以上の外部結合に接続できません.すなわち、A、B、Cテーブル、BテーブルはAに対して展開される.
また、cテーブルを基準にBテーブルを拡張することはできません.
WHERE A=B(+)
AND C=D(+)

は許可されていません
(通常の外部結合形式を使用)
SELECT列リスト
FROM表名1[別名1]、表名2[別名2],[,表名3[別名3],...]
WHERE別名1.カラム名1(+)=別名2.カラム名2--=(+)も使用できます.
:
(ANSI外部結合形式を使用)
SELECT列リスト
FROMテーブル名1[別名1]
FULL|RIGHT|LEFTOUTER JOIN表名2[別名2]ON(接続条件[AND一般条件])
:
[WHERE一般条件];
-- FULL|RIGHT|LEFT
-FULL:両方とも足りない
-RIGHT:OUTER JOIN節の「表名2」はFROM節の「表名1」よりも多い
-LEFT:OUTER JOIN節の「表名2」のデータはFROM節の「表名1」より多い
**一般外部結合を使用する場合、結合条件と一般条件を同時に適用した場合、正しい結果は返されません-(ソリューション=>サブクエリまたはANSI外部結合を使用)
使用例)すべての分類商品の数量を問い合わせる--「すべて」外部結合

      SELECT COUNT(DISTINCT PROD_LGU) FROM PROD; 
 --상품테이블에서 분류코드는 6개 LPROD 분류코드는 9개
 --분류코드는 A,B 두테이블둘다 있음. SELECT절에서 쓸때 무조건 많은 쪽 테이블걸 써야함.
       
       SELECT A.LPROD_GU AS 분류코드, 
              A.LPROD_NM AS 분류명, 
              COUNT(B.PROD_ID) AS "상품의 수 " 
              -- 외부조인할 때 COUNT쓸때 *쓰면안됨. NULL값을 같는 행도 한줄로 취급되어짐. 
             --  때문에 Null값이 없는 기본키 PROD 컬럼명하나 써줌
         FROM LPROD A, PROD B
        WHERE A.LPROD_GU=B.PROD_LGU(+)
        GROUP BY A.LPROD_GU, A.LPROD_NM
        ORDER BY 1;

(ANSI FORMAT)

      SELECT A.LPROD_GU AS 분류코드, 
             A.LPROD_NM AS 분류명, 
             COUNT(B.PROD_ID) AS "상품의 수"                                           
        FROM LPROD A 
       LEFT OUTER JOIN PROD B ON(A.LPROD_GU=B.PROD_LGU)
       GROUP BY A.LPROD_GU, A.LPROD_NM
       ORDER BY 1;

使用例)2005年4月のすべての商品の購入統計を調べてください.
Aliasは商品コード、商品名、購入数量(統計)と購入金額を提供します.

--26개밖에 안나옴
       SELECT DISTINCT BUY_PROD
         FROM BUYPROD
        WHERE BUY_DATE BETWEEN TO_DATE('20050401') 
        AND TO_DATE('20050430') -- 4월에 매입된 상품 26개

   SELECT B.PROD_ID AS 상품코드,  
   -- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
          B.PROD_NAME AS 상품명, 
          SUM(A.BUY_QTY) AS 매입수량, 
          SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
          -- PROD의 매입단가를 쓰는게 좋음 
     FROM BUYPROD A, PROD B
    WHERE A.BUY_PROD(+)=B.PROD_ID --외부조인
      AND A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
    GROUP BY B.PROD_ID, B.PROD_NAME
    ORDER BY 1 ;

(ANSI)--結果74行

       SELECT B.PROD_ID AS 상품코드,  
- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
                 B.PROD_NAME AS 상품명, 
               NVL(SUM(A.BUY_QTY),0) AS 매입수량, 
               NVL(SUM(A.BUY_QTY*B.PROD_COST),0) AS 매입금액
               -- PROD의 매입단가를 쓰는게 좋음 
         FROM BUYPROD A
         RIGHT OUTER JOIN PROD B ON (A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430'))
         GROUP BY B.PROD_ID, B.PROD_NAME
         ORDER BY 1 ;

(サブクエリ)--結果74行

      SELECT B.PROD_ID AS 상품코드, 

 -- 상품코드는 A,B 테이블 둘다 있음 더많은 PROD 테이블의 상품코드를 써야함.
                 B.PROD_NAME AS 상품명, 
               NVL(D.CAMT,0) AS 매입수량, 
               NVL(D.SAMT,0) AS 매입금액 
               -- PROD의 매입단가를 쓰는게 좋음 
         FROM PROD B, 
        (--2005년 4월 상품별 매입집계=내부조인. 기간(일반조건)을 처리해줌
                 SELECT A.BUY_PROD AS BID,
                        SUM(A.BUY_QTY) AS CAMT,
                        SUM(A.BUY_QTY*C.PROD_COST) AS SAMT
                   FROM BUYPROD A, PROD C
                  WHERE A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
                    AND A.BUY_PROD=C.PROD_ID 
                  GROUP BY A.BUY_PROD)D
        WHERE B.PROD_ID=D.BID(+)
        ORDER BY 1;

使用例)2005年4月の全商品の販売統計を調べてください
商品コード、商品名、販売数量(統計)、販売金額

  --2005년 4월 모든 상품별 매출
   
   -- AND A.BUY_PROD=B.PROD_ID AND A.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430')
        GROUP BY B.PROD_ID, B.PROD_NAME
        ORDER BY 1 ;

(一般結合)

 SELECT A.PROD_ID AS 상품코드, 
           A.PROD_NAME AS 상품명, 
           SUM(B.CART_QTY) AS 매출수량, 
           SUM(A.PROD_PRICE*B.CART_QTY) AS 매출금액
      FROM PROD A, CART B
     WHERE B.CART_PROD(+)=A.PROD_ID
       AND A.PROD_ID=B.CART_PROD
       AND B.CART_NO LIKE '200504%'
  GROUP BY A.PROD_ID, A.PROD_NAME
  ORDER BY 1;

(ANSI)

 SELECT A.PROD_ID AS 상품코드, 
         A.PROD_NAME AS 상품명, 
         NVL(SUM(B.CART_QTY),0) AS 매출수량, 
         NVL(SUM(A.PROD_PRICE*B.CART_QTY),0) AS 매출금액
  FROM PROD A
  LEFT OUTER JOIN CART B ON(A.PROD_ID=B.CART_PROD 
  AND B.CART_NO LIKE '200504%')
  GROUP BY A.PROD_ID, A.PROD_NAME
  ORDER BY 1;

(サブクエリ)

    SELECT A.PROD_ID AS  상품코드, 
           A.PROD_NAME AS 상품명, 
           NVL(D.BB,0) AS 매출수량, 
           NVL(D.CC,0) AS 매출금액
      FROM PROD A, ( --2005년 4월 일반조건
                    SELECT B.PROD_ID AS AA , 
                           SUM(C.CART_QTY) AS BB , 
                           SUM(B.PROD_PRICE*C.CART_QTY) AS CC
                      FROM PROD B, CART C
                     WHERE C.CART_NO LIKE '200504%'
                       AND B.PROD_ID=C.CART_PROD
                     GROUP BY B.PROD_ID)D
     WHERE A.PROD_ID=D.AA(+)
     ORDER BY 1;

使用例)2005年4月の全商品の販売統計を調べてください
Aliasは商品コード、商品名、購入数量、販売数量、購入金額、販売金額を提供します

       SELECT A.PROD_ID AS 상품코드, 
              A.PROD_NAME AS 상품명, 
              NVL(SUM(B.BUY_QTY),0) AS 매입수량, 
              NVL(SUM(C.CART_QTY),0) AS 매출수량, 
              NVL(SUM(B.BUY_QTY*A.PROD_COST), 0) AS 매입금액, 
              NVL(SUM(C.CART_QTY*A.PROD_PRICE),0) AS 매출금액
         FROM PROD A
         LEFT OUTER JOIN BUYPROD B ON(A.PROD_ID=B.BUY_PROD 
                                      AND B.BUY_DATE BETWEEN TO_DATE('20050401') AND TO_DATE('20050430'))
         LEFT OUTER JOIN CART C ON(C.CART_PROD=A.PROD_ID 
                                   AND C. CART_NO LIKE '200504%') 
         GROUP BY A.PROD_ID, A.PROD_NAME
         ORDER BY 1;

使用例)hr勘定科目の表を使用して、「すべて」部門の従業員数と平均勤続年数、平均給与を問い合わせる
--すべての->外部結合、部門->グループ・セクション、人数->COUNTカッコ内のプライマリ・キー
Aliasは部門コード、部門名、人員数、平均勤務年数、平均賃金です.

   SELECT B.DEPARTMENT_ID AS 부서코드, --EMP, DEPT에 둘다 있음. EMP 쓰면 널값 16개 DEPT 쓰면 1개의 널값 양쪽이 다 부족
          B.DEPARTMENT_NAME AS 부서명, 
          COUNT(A.EMPLOYEE_ID) AS 인원수, 
          NVL(ROUND(AVG(EXTRACT(YEAR FROM SYSDATE) -EXTRACT(YEAR FROM A.HIRE_DATE))),0) AS 평균근무년수, 
          NVL(ROUND(AVG(A.SALARY)),0) AS 평균급여
     FROM HR.EMP A
     FULL OUTER JOIN HR.DEPT B ON(A.DEPARTMENT_ID= B.DEPARTMENT_ID) -- 양쪽이 다 부족해서 FULL OUTER JOIN 
     GROUP BY  B.DEPARTMENT_ID, B.DEPARTMENT_NAME
     ORDER BY 1;

使用例)2005年6月のすべての会員の購入状況である「すべて」の外部署名を確認してください.
Aliasは会員番号、会員名、購入数、購入金額です.

     SELECT A.MEM_ID AS 회원번호, 
          A.MEM_NAME AS 회원명, 
          NVL(SUM(B.CART_QTY),0) AS 구매수량, 
          NVL(SUM(B.CART_QTY*C.PROD_PRICE),0) AS 구매금액
     FROM MEMBER A  --"회원별"-MEMBER테이블이 젤 많이 정보 가짐
      LEFT OUTER JOIN CART B ON (A.MEM_ID = B.CART_MEMBER AND B.CART_NO LIKE '200506%')
      LEFT OUTER JOIN PROD C ON (C.PROD_ID=B.CART_PROD) -- A,B조인결과와 조인
       GROUP BY A.MEM_ID, A.MEM_NAME
       ORDER BY 1;