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

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

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

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

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

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

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

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

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

leftjoin

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

INNER JOIN(内部結合)

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

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

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

すべての基本は、

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

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

外部結合

内部結合

集計関数(aggregate function)

集計関数AVG, COUNT, MIN, MAX, SUM以外の値はすべて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でサンプル的に取得するのが簡単で、文字列について使用すると辞書順での最大値を取得します。

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

サブクエリ

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

参照の場合

更新の場合

CASE式

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

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

  1. CASEでどのフィールドに値をセットするか選択。
  2. 他のフィールドが歯抜けになるのでGROUP化。
  3. 複数レコードの合計値ならばSUMだし確定値ならMAXを使用。