SQLによる外部結合と内部結合

LEFT JOINは左のテーブルをすべて抽出した上で、これを基準としてON以下の外部結合条件に合致すれば右テーブルのレコードを結合し、INNER JOINはWHEREと同じ完全内部結合であり、ON以下の内部結合条件が両方のテーブルに合致する場合のみレコードを結合します。

WHEREによる結合条件の場合、テーブルを先に並べて結合条件は最後のWHEREで一箇所にまとめる違いがあります。

業務システムの実績データを分析する際にLEFT JOINを使うケースが多いのは、実績(左)に含まれない不足情報をマスタ(右)から追加する、というニーズが多いからです。

ON以下の結合条件はマスタ(右)のプライマリーキーであるはずはずですから、外部結合レコード件数は実績(左)と同じになり、実績のないマスタがレコードを作ることはありません。レコードが増えたら結合条件が不足しているかキーが重複しているかのどちらかです。

集計関数AVG, COUNT, MIN, MAX, SUM以外の値はすべてGROUP BYに含まれている必要があります。

普通のCASE式は値の置き換えに使い、集合関数MAXと併用すると縦並びテーブルを横並びテーブルに変換できますが、考え方はCASEで条件指定で値を取得し、GROUP化で統合することにより条件に合わないとき空データが消えます。

LEFT JOIN(外部結合 LEFT OUTER JOINの略)

まずは実績テーブルと品目マスタを以下のように生成します。

CREATE TABLE "DEMO"."ACTUAL" 
   (	"品目コード" VARCHAR2(20 BYTE), 
	"実績" NUMBER
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
CREATE TABLE "DEMO"."ACTUAL" 
   (	"品目コード" VARCHAR2(20 BYTE), 
	"実績" NUMBER
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

実績テーブルに不足する品目名を品目マスタから追加するということは、左の実績テーブルを優先させる外部結合になります。

SELECT 
    * 
FROM 
    ACTUAL A 
LEFT JOIN 
    ITEM_MAST B 
ON 
    A.品目コード=B.品目コード

ただし左右を入れ替えると左にくる品目マスタが優先されますが、右の実績テーブルには結合条件のキーである品目コードが重複しているため、ダブルに出力されます。

LEFT JOINでテーブルを結合していく場合、結合前と後のレコード数が同じであるチェックが必要であり、増えていればたいていは右のテーブルのプライマリーキーが重複しているか結合条件が不足しているかです。

SELECT 
    * 
FROM 
    ITEM_MAST A 
LEFT JOIN 
    ACTUAL B 
ON 
    A.品目コード=B.品目コード

leftjoin

ちなみにOracleだと外部結合のLEFT JOINがうまくいかないケースがあるので(+)付きのサブクエリをよく使います。これが(+)なしだと内部結合になってしまいます。

SELECT 
    T1.ITM_CD,
    T2.ITM_NAME
FROM 
    T1 
LEFT JOIN 
    T2
ON 
    T1.ITM_CD = T2.ITM_CD;

または
SELECT 
    T1.ITM_CD,
    T2.ITM_NAME
FROM 
    T1,
    T2
WHERE 
    T1.ITM_CD = T2.ITM_CD(+);



INNER JOIN(内部結合)

WHEREによる結合条件と同じで、ON以下の結合条件に合致しなければレコードを出力しません。左に実績、右にマスタの場合は結果は同じですが、左にマスタ、右に実績の場合は実績のないマスタはレコードを出力しません。内部結合では両方の表に存在するものだけが出力されます。
INNERJOIN

上述のとおりINNER JOINのONの結合条件は、「完全に満たす」という意味でWHEREで結ぶ結合条件と同じです。サブクエリの場合、テーブルを先に並べて結合条件は最後のWHEREで一箇所にまとめて書くところが外部結合・内部結合との違いです。

SELECT 
    * 
FROM 
    ITEM_MAST A, 
    ACTUAL B 
WHERE 
    A.品目コード=B.品目コード;

比較するテーブルの絞込条件を追加

すべての基本は、

  • 外部結合(LEFT JOIN)は左のテーブルをすべて抽出し、これを基準にON結合条件で右のテーブルのキーをチェックし、合致するレコードを結合。よってキーが存在せず結合できなくてもレコードをNull値で追加し、キーが重複していればその分重複して結合され、レコード件数が増える。
  • 内部結合(INNER JOIN)はON結合条件で両方のテーブルをチェックし、合致するレコードを結合。よってキーが重複していればその分重複して結合されレコードが増える。

あとは必要に応じて対象となるテーブルに絞込の条件をつけて絞っていくだけで、基本的な外部結合と内部結合の考え方は一緒です。

外部結合

SELECT 
    * 
FROM 
    (SELECT * FROM ACTUAL WHERE 実績 >= 10) A 
LEFT JOIN 
    (SELECT * FROM ITEM_MAST WHERE LEFT(品目名,1)='C')  B 
ON 
    A.品目コード=B.品目コード

内部結合

SELECT 
    * 
FROM 
    (SELECT * FROM ACTUAL WHERE 実績 >= 10) A, 
    (SELECT * FROM ITEM_MAST WHERE LEFT(品目名,1)='C') B,
    (SELECT * FROM ITEM_DETAIL WHERE LEFT(品目名,1)='C') C
WHERE 
    A.品目コード=B.品目コード
AND
    B.品目コード=C.品目コード;

または
SELECT 
    * 
FROM 
    (SELECT * FROM ACTUAL WHERE 実績 >= 10) A
INNER JOIN
    (SELECT * FROM ITEM_MAST WHERE LEFT(品目名,1)='C') B 
ON
    A.品目コード=B.品目コード;
INNER JOIN
    (SELECT * FROM ITEM_DETAIL WHERE LEFT(品目名,1)='C') C
ON
    B.品目コード=C.品目コード;

集計関数(aggregate function)

集計関数AVG, COUNT, MIN, MAX, SUM以外の値はすべてGROUP BYに含まれている必要があります。

SELECT 
    品目コード,
    月, 
    SUM(製造数量) AS 製造合計 
FROM 
    受払実績T 
GROUP BY 
    品目コード
;

SELECTでGROUP BYで指定した列以外を取得しようとした場合のエラー

  • You tried to execute a query that doesn’t include the specifiedexpression ‘ITEM’ as part of an aggregate function
    集計関数の一部として指定された式’ITEM’を含んでいないクエリを実行しようとしました

よってSELECTに月も表示したい場合はMAXでサンプル的に取得するのが簡単で、文字列について使用すると辞書順での最大値を取得します。

SELECT 
    品目コード,
    月, 
    SUM(製造数量) AS 製造合計 
FROM 
    受払実績T 
GROUP BY 
    品目コード,
    月;
または
SELECT 
    品目コード,
    MAX(月), 
    SUM(製造数量) AS 製造合計 
FROM 
    受払実績T 
GROUP BY 
    品目コード
;

GROUP BY以下に指定する集計項目の順番は「品目別月別」なら「品目コード, 月」、「場所別製品グループ別」であれば「場所, 製品グループ」の順に並べます。

サブクエリ

SQL文の中にSELECT句を入れ子にして埋め込むのがサブクエリです。LEFT JOINはONでテーブル間を接続しますが、サブクエリはテーブルをカンマで並べてWHEREで接続します。

参照の場合

SELECT 
    A.ITM_CD,
    B.ACT_QTY
FROM
    (
    SELECT 
        * 
    FROM 
        MASTER
    ) A,
    (
    SELECT 
        * 
    FROM 
        ACTUAL
    ) B 
WHERE 
    A.ITM_CD=B.ITM_CD;

更新の場合

UPDATE 
    配賦比率マスタ A 
SET A.比率=
    (
    SELECT 
        SUM(直接作業時間) 
    FROM 直接工数テーブル 
    WHERE 
        YM='01-JUL-15' 
    AND 
        LOC_CD='ASSY01'
    ) 
WHERE 
    A.配賦先部門コード='ASSY01';

CASE式

まず普通のCASE式は値の置き換えに使い、当然ですが結合条件がプライマリーキーであれば結合後のレコード数が増えることはありません。

SELECT 
    TO_CHAR(d.DLV_ACT_DT,'YYYYMM') AS 年月,
    (
    CASE b.ITM_TYP
        WHEN '1' THEN '06'
        WHEN '2' THEN '07'
        ELSE '09'
        END
        ) 
        AS 区分コード,
    (
    CASE b.ITM_TYP
        WHEN '1' THEN '製品外注'
        WHEN '2' THEN '加工外注'
        ELSE 'その他外注'
        END
        ) 
        AS 区分名,
    d.ITM_CD AS 製品コード, 
    b.EXT_ITM_NM AS 型式, 
    b.ITM_NM AS 品名, 
    d.DLV_ACT_DT AS 検収日, 
    d.INPT_QTY AS 個数, 
    d.ARR_UPRI AS 単価, 
    d.ARR_AMT AS 金額, 
    g.SPPL_CD AS 支給先コード, 
    f.LOC_NM AS 支給先名称, 
    f.LOC_TYP AS 場所種類
FROM 
    入荷実績 
LEFT JOIN 
    発注実績明細 e 
ON 
    d.PO_D_NO=e.PO_D_NO
LEFT JOIN 
    発注実績ヘッダー g 
ON 
    d.PO_NO=g.PO_NO
LEFT JOIN 
    品目マスタ b 
ON 
    d.ITM_CD=b.ITM_CD
LEFT JOIN 
    場所マスタ f 
ON 
    g.SPPL_CD=f.COMPANY_CD
WHERE 
    EXTRACT(YEAR FROM d.DLV_ACT_DT)='2015' 
AND 
    EXTRACT(MONTH FROM d.DLV_ACT_DT)='7' 
AND 
    (
    b.ITM_TYP='1' 
    OR 
    b.ITM_TYP='2'
    )
ORDER BY 
    検収日, 
    製品コード;

CASE式を集合関数と併用するとテーブルの2次元表化、つまり縦並びテーブルを横並びテーブルに変換できますが、考え方は以下のとおりです。

  1. CASEでどのフィールドに値をセットするか選択。
  2. 他のフィールドが歯抜けになるのでGROUP化。
  3. 複数レコードの合計値ならばSUMだし確定値ならMAXを使用。
SELECT 
    a.ITM_CD AS 製品コード,
    MAX
    (
    CASE a.QUAL_TYP 
        WHEN '0' THEN a.ACT_QTY 
        ELSE 0 
    END
    ) AS 良品数,
    MAX
    (
    CASE a.QUAL_TYP 
    WHEN '1' THEN a.ACT_QTY 
    ELSE 0 
    END
    ) AS 不良品数
FROM 
    (
    SELECT 
        * 
    FROM 
        製造実績 
    WHERE 
        EXTRACT(YEAR FROM PROD_ACT_APP_DT)='2015' 
    AND 
        EXTRACT(MONTH FROM PROD_ACT_APP_DT)='7'
    ) d
LEFT JOIN 
    (
    SELECT 
        * 
    FROM 
        製造出来高 
    WHERE 
        SUBSTR(ITM_CD,9,5)='-001-') a 
    ON 
        a.PROD_ACT_NO=d.PROD_ACT_NO
    GROUP BY 
        a.ITM_CD,
        a.PROD_ACT_NO
;
SELECT 
    B.LOC_CD AS コストC, 
    E.CTG_A AS 製品G, 
    SUM(A.ACT_QTY) AS 生産数, 
    SUM(F.ACTIVITY) AS 直接工数,
    SUM
        (
        CASE C.EXPNS_CD 
        WHEN '200' THEN C.ACT_COST 
        ELSE 0 
        END
        ) AS 直接労務費,
    SUM
        (
        CASE C.EXPNS_CD 
        WHEN '400' THEN C.ACT_COST 
        ELSE 0 
        END) AS 製造間接費2
FROM 
    (
    SELECT 
        * 
    FROM 
        最終品目T 
    WHERE 
        CALC_NO='AL130' 
    AND 
        REC_TYP='0'
    ) A
LEFT JOIN 
    (
    SELECT 
        * 
    FROM 
        使用数量T 
    WHERE 
        CALC_NO='AL130' 
    AND LVL='0'
    ) B 
ON 
    A.REC_GRP=B.REC_GRP
LEFT JOIN 
    (
    SELECT 
        * 
    FROM 
        費用別発生金額T 
    WHERE 
        CALC_NO='AL130'
    ) C 
ON 
    B.REC_NO=C.REC_NO
LEFT JOIN 
    (
    SELECT 
        * 
    FROM 
        品目M
    ) E 
ON 
    A.ITM_CD = E.ITM_CD 
AND 
    TO_CHAR(A.YM,'YYYY')=TO_CHAR(TERM,'YYYY')
LEFT JOIN 
    (
    SELECT 
        * 
    FROM 
        直接工数T
    ) F 
ON 
    A.ITM_CD=F.ITM_CD 
AND 
    B.LOC_CD=F.LOC_CD 
AND 
    A.YM=F.YM
WHERE 
    LENGTH(F.LOC_CD) > 0
GROUP BY 
    B.LOC_CD, 
    E.CTG_A
ORDER BY 
    B.LOC_CD
;