Home > インドネシアのITサービス > SQLによる外部結合と内部結合

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

2012/05/20

SQL

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

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

業務システムの実績データを分析する際にLEFT JOINを使うケースが多いのは、実績（左）に含まれない不足情報をマスタ（右）から追加する、というニーズが多いからで、この場合ON以下の結合条件はマスタ（右）のプライマリーキーであるはずで、外部結合結果のレコード件数は実績（左）と同じになり、実績のないマスタがレコードを作ることはありません。

レコードが増えたら結合条件が不足しているか、マスタ（右）のキーが重複しているかのどちらかですが、ヘッダー（左）に対する明細（右）のような1対Nのテーブル同士を結合する場合は、外部結合結果のレコード件数はヘッダー（左）よりも多くなります。

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

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

ジャカルタ

インドネシアのITサービス

インターネット技術の急速な発展と普及により、優秀なIT人材を輩出することで知られるジャカルタのビヌス大学（BINUS）やバンドゥンのバンドゥン工科大学、インドネシアコンピューター大学（UNIKOM）の学生の多くがインターネット・WEB業界やソフトウェア業界を志望するようです。

続きを見る

LEFT 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.品目コード

LEFT JOIN

ちなみに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以下の結合条件に合致しなければレコードを出力しません。左に実績、右にマスタの場合は結果は同じですが、左にマスタ、右に実績の場合は実績のないマスタはレコードを出力しません。内部結合では両方の表に存在するものだけが出力されます。
INNER JOIN

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

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

LEFT JOIN外部結合とINNER JOIN内部結合のポイントは以下のとおりです。

  • 外部結合（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.品目コード;

GROUPY BY（集計）

集計関数（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
;

CASE式と集合関数

日常生活で使う言葉としての「結合」と「統合」はほぼ同義語ですが、SQLの場合は結合はJOIN、統合はUNIONを使い、表示結果が異なります。
CASE式と集合関数

CASEでどの列に値をセットするか選択して、他の列が歯抜けにならないように集合関数でGROUP化します。

CASE式ではELSEで条件に合致しない場合も0をセットしてしまうので、AとBが共に0の場合は合致する品目自体が存在しないため、「AとBのどちらかが0でない場合」という抽出条件を追加します。

SELECT
  ITM_CD,
  A,
  B
FROM
  (SELECT
    ITM_CD,
    SUM(CASE ROW_NO
    WHEN 6 THEN AMOUNT
    WHEN 7 THEN AMOUNT
    WHEN 12 THEN AMOUNT
    ELSE 0
    END) AS A,
    SUM(CASE ROW_NO
    WHEN 18 THEN AMOUNT
    WHEN 19 THEN AMOUNT
    ELSE 0
    END) AS B
  FROM
    A_COST_ITEM_TEMP

  GROUP BY
    ITM_CD
  )  
WHERE
  A <> 0
OR
  B <> 0
;

完全外部結合

CASE式と集合関数で縦並びテーブルを横並び化するということは、列ごとのSELECT結果を完全外部結合FULL OUTER JOINしているのと同じことです。

SELECT 
  a.ITM_CD, 
  b.ITM_CD, 
  nvl(a.Tot1,0), 
  nvl(b.Tot2,0) 
FROM 
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as Tot1 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 6 
  OR 
    ROW_NO = 7 
  OR 
    ROW_NO = 12) 
  GROUP BY 
    ITM_CD) a 
  FULL OUTER JOIN 
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as Tot2 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 18 
  OR 
    ROW_NO = 19) 
  GROUP BY 
    ITM_CD) b 
  ON 
    a.ITM_CD = b.itm_cd;

通常は「左テーブルを優先して右テーブルの主キー（プライマリーキー）と合致することを結合条件として左テーブルに列を追加することを目的とする」左外部結合LEFT (OUTER) JOINしか使う機会はありませんが、「右テーブルを優先して左テーブルの主キーと合致することを結合条件として右テーブルに列を追加することを目的とする」右外部結合RIGHT (OUTER) JOINもあります。

そして左右の外部結合のコンビネーションとして「左右の主キーを合致してもしなくても結合する完全外部結合FULL OUTER JOINがあり、今回のように「左右両方のビューのどちらかに1個でも主キーがあれば結合する」という場合に使用します。

UNION ALL（統合＝和集合）

「左右両方のビューのどちらかに1個でも主キーがあれば結合して縦並びレコードを横並び」するということは「主キー以外の情報列の並び順が合致しない左右のビューを統合UNION ALLして主キーでグループ化する」ことと同じです。

SELECT 
  A.ITM_CD, 
  SUM(T1.A), 
  SUM(T1.B)
FROM
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as A, 
    SUM(nvl(0,0)) as dummy1 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 6 
  OR 
    ROW_NO = 7 
  OR 
    ROW_NO = 12) 
  GROUP BY 
    ITM_CD
  UNION ALL
  SELECT 
    ITM_CD, 
    SUM(nvl(0,0)) as dummy2, 
    SUM(nvl(AMOUNT,0)) as B
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 18 
  OR 
    ROW_NO = 19) 
  GROUP BY 
    ITM_CD) T1
GROUP BY 
  ITM_CD;

統合UNIONの場合、左テーブルの列名を優先して同じ並び順かつデータ型が同じ右テーブルを統合しますので、左右のビューにダミー列を追加して両方の情報列を取得できるようにしますが、主キーが重複したレコードも取得するためにUNION ALLを使用します。

NULLのせいで意図しない結果にならないために

NULL は「何も存在しない」を意味するのであってゼロや空文字が意味する「存在しうるところに存在しない」とは違うものです。迷子がNULLでホームレスがゼロか空文字という例えで合っていると思います。

「ゼロを発見したのはインド人」とよく言われますが、数学的にいうと「何もない（NULL）に対してゼロという数字を与えた」ということになるのだと思います。ゼロがないと9から先の表現大変そう・・・・。

  1. 数学的に位取りができること
  2. 哲学的にない状態があるという事実を表現すること

SQLでは0除算はエラーですがNULL除算はNULLが返されエラーになりません。だからエラーを起こさないためには値が0だったらNULLに置き換えればいい訳で、このNULLIFはANSI標準のSQL関数ですべてのDBがサポートしています。

  • Access/SQLServer/Oracle/MySQL : NULLIF（ VAL, 0 ）

上述のようにSQLではNULLを含む加減乗除はNULLを返しますので、SUM集合関数を使って合計するときにNULLだと都合が悪いので、今度は逆にNULLなら0に変換してあげます。

  • Access : ISNULL(VAL, 0)
  • MySQL : IFNULL(VAL, 0)
  • Oracle : NVL (VAL, 0)

SQLではNULLとの加減乗除だけでなく比較演算子もNULLを返し、等号や不等号は成立しません。よってWHERE条件でNULLとの比較を行うときはIS NULLやIS NOT NULLを使う必要があります。

  • WHERE VAL='0003';
  • WHERE VAL<>'0003';
  • WHERE VAL!='0003';
  • WHERE VAL IS NOT '0003'; エラー
  • WHERE VAL IS NULL; OK
  • WHERE VAL IS NOT NULL; OK
  • WHERE VAL != NULL; NULLを返す
  • WHERE VAL <> NULL; NULLを返す

DBごとに微妙に違うSQL構文

SQL文作成時にDBによって微妙に構文が異なっており、なんで統一してくんないの？といつも思うのですが、せっかくなのでよく使う構文だけまとめてみます。

日付

■日付型レコードをYYYYMM形式のテキストフォーマット出力

  • Access : format(dat, 'YYYYMM')
  • MySQL : DATE_FORMAT(dat, '%Y%m')
  • Oracle : TO_CHAR(dat, 'YYYYMM')

■日付から年を取り出す

  • Access/SQLServer/MySQL : YEAR(dat)
  • Oracle : EXTRACT(YEAR FROM dat)

文字列

■文字列への型変換

  • Access : CStr(num)
  • SQLServer : CONVERT(VARCHAR, num)
  • Oracle : TO_CHAR(num)
  • MySQL : CAST(num AS CHAR)

■数値への型変換

  • Access : CInt(sgt)
  • SQLServer : CONVERT(INT, stg)
  • Oracle : TO_NUMBER(stg)
  • MySQL : CAST(stg AS INT)

■文字列の切り取り

  • Access : MID(stg, 3,2)
  • SQLServer/MySQL : SUBSTRING(stg, 3, 2)
  • Oracle : SUBSTR(stg, 3, 2)

■文字列の結合

  • Access/SQLServer : stg1 + stg2またはstg1 || stg2またはstg1 & stg2
  • MySQL/Oracle : CONCAT(stg1, stg2)

■文字列の置き換え

  • Access/SQLServer/Oracle : REPLACE(stg, 't', 'p')
  • MySQL : TRANSLATE(stg, 't', 'p')

■文字列の文字数取得

  • Access/SQLServer : LEN(stg)
  • Oracle : LENGTH(stg)
  • MySQL : CHAR_LENGTH(stg)