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

情報システム

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

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)は左のテーブルをすべて抽出し、これを基準に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
;





おすすめ記事一覧

大統領選挙で考えたギャップにハマるということ 1

ギャップにキュンとするというのは人間の本能みたいなもので、ジョコウィの私利私欲のない素朴なおじさん像と、その実強力なリーダーシップを発揮する実務派という内面が、一見普通の人だが実はスゴイというギャップ好きのインドネシア人に大ウケして、大衆は一種の集団催眠状態にあるようです。

情報の質のレベル 2

見える化された結果を共有化することで問題点が共通認識されますが、共有化が進むことで情報の持つ希少価値が薄れて困る人間がいる場合、有益な情報を独占することでポジションを高めようという政治力が働きます。

3

日本人がインドネシアに来ると、インドネシア人ののんびり加減にイライラするというのは昔からよく聞く話で、インドネシア在住日本人にとってのバイブル的小説である深田祐介著「ガルーダ商人」の中でも、インドネシア宗教省の高官が日本人とインドネシア人を自宅に招待する際に、インドネシア人向けの招待状には、遅刻することを前提にパーティ開始時間を三十分早く書いておくという記述があるほどです。

宗教によって異なる「死んだらどうなる」の考え方 4

キリスト教もイスラム教もともにユダヤ教から派生した宗教であり、それぞれイエス・キリスト(本人が神)またはアッラーという唯一無二の神を信じます。

株価操作なんてインドネシア株では当たり前 5

株価は売り注文と買い注文により変動し、大量の売り注文を買う注文がたくさん入れば、他の投資家達は「俺も俺も」と続くことで株価が上がります。

心臓に毛が生えたインドネシア人のずうずうしい転職活動を応援してみた 6

インドネシア人は秘密の話は誰かに暴露しないと精神の安定を保てない人が多いため、内緒の話に情報の希少性は少なく信憑性も低いことが多いので、「ここだけの話」という枕詞付きで聞かされる話は話半分に聞いておいたほうがいいかもしれません。

日系企業のインドネシアでの存在意義 7

今のまま日本の人口減が続けば、内需は縮小の一途をたどるわけで、そうなると日本国内市場だけで生き残るのは難しいと判断する国内企業が、海外市場に活路を見出そうとするのは必然です。

チャンスはあるが勝てる分野を見つけるのが難しい 8

実際にインドネシアに住んでみて、自分で動いて人と話しをして、現地の事情を少しずつ理解していくにつれて、インドネシアで起業することが意外と手強いことに気づき、その難しさの原因は、高い送料と関税であったりローカル企業との競争であったり、就労ビザ(IMTA)や外国人技能開発基金(DPKK)などのランニングコストの高さであったりします。

インドネシアのシステムインテグレーション業界 9

先日JETRO(日本貿易振興機構)さんと、インドネシアの中小企業のIT投資について意見交換させていただく機会をいただいたのですが、そこで「システム投資のコストメリットはどのように説明できるのか」という、システムインテグレーターの存在価値にも関わる重要な問題提起がありました。

肉体と精神と心と魂 10

「Body and Soul」といえば、昨日の内閣改造に伴う人事で内閣府政務官に内定した自民党の今井絵理子参議院員がメンバーだったSPEEDのデビュー曲であり、インドネシアの老舗女性ファッションブランド名でもあります。

ジャカルタのラーメン市場 11

僕がインドネシアに初めて来たのが1997年10月、インドネシア語は分からないし、仕事は辛いし、周囲の人間は理不尽だし、一時期日本に帰りたくて仕方がない時期がありましたが、当時自分をかろうじてインドネシアに繋ぎ止める心の支えとなっていたのが、協栄プリンスビル(今のWisma Keiai)の日本食レストラン「五右衛門」であり、ここでキムチラーメンを食べることが唯一の楽しみと言っても過言ではありませんでした。

ブランド力、技術力、資金力の3要素 12

1998年のジャカルタ暴動後、ルピアが暴落し海外からのドル建て債務を抱えた国内企業が利子の支払いに苦しんでいた頃、僕は外貨が獲得できるインドネシアでの新しいビジネスを探していました。

日本とインドネシアの間でのタイムマシン経営が通じなくなっている件 13

先進国と後進国との間にある流行のタイムラグを利用して、先進国での成功例を後進国で実践するビジネスモデルをタイムマシン経営といいますが、インターネットの普及に伴い情報がフラット化してしまい、モノと情報のタイムラグが限りなく小さくなった今、先駆者である中小零細同業他社が乱立し市場が出来上がったところに、後発の大手が参入し先発零細を駆逐していく、という典型的な負けパターンにはまります。

サリナデパートとマクドナルド 14

本日5月10日を最後にインドネシアのマクドナルド第1号店であるサリナデパート店(Sarinah)が閉店になりますが、ジャカルタのショッピングモールが新しいコンセプトでモダンにリニューアルされ続ける中で、僕がインドネシアに来たばかりの20数年前には、若者の待ち合わせ場所の定番でもあったサリナデパートやブロックMのパサラヤ(Pasaraya)などは完全に時代に取り残されてしまいました。

不景気の歴史 15

僕がインドネシアに来てからこれまで何度か経済不況を見てきましたが、今回の新型コロナウィルスの感染拡大により、間違いなく景気後退しますので、数年後にはこれがコロナショックとかコロナ不況とか呼ばれるようになるのかもしれません。

日本のバブル経済崩壊後とインドネシアの通貨危機後 16

自分が大学に入学したのがバブル経済末期の1991年、土地も株価もMAX爆上げして、三菱地所がアメリカの象徴であるロックフェラーセンタービルを買収し、ジュリアナ東京でワンレンボディコン(登美丘高校ダンス部のバブリーダンスみたいなやつ)のお姉さん達が扇子振って踊っている時期でした。

内需と外需の自国経済に及ぼす影響 17

公共事業投資を行っても、お金が企業内や個人の貯蓄に滞留してしまい国内消費が増えないのが日本の状況であり、国内消費は増えても消費材の輸入品比率が高く、国内資産が海外に流出しているのがインドネシアの状況です。

2019年の総選挙を前にインドネシア政治史のおさらい 18

来年の大統領選挙(Pemilu Pilpres Pileg Indonesia 2019)に向けての選挙運動(Kampanye)を解禁するにあたり、投票用紙に印字される順番はジョコウィ現職大統領・マフル副大統領候補組が1番、プラボウォ大統領候補・サンディアガウノ副大統領候補組が2番と決まりました。

コーヒーをもっと楽しくもっと美味しく 19

インドネシアは北回帰線と南回帰線をはさむコーヒーベルトに位置するコーヒー栽培に適した国で、1602年の東インド会社の進出を契機にオランダの植民地支配が300年以上続き、その間アラビカ種のコーヒーが持ち込まれ、気候のいい高原地帯で栽培が開始されました。

インドネシア人の悪魔祓い 20

人間誰しも自分の中に悪魔が潜んでおり、それが何らかのきっかけで表面に出て来るという考え方自体には、背景に宗教が有るか無いかの違いだけで、基本的に理解できる話であり、それを信じるか信じないかは別として、そういう考えがあることを認めることは大切なことだと思います。

-情報システム

© 2020 バテラハイシステム Powered by STINGER