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サービス
続きを見る
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.品目コード
ちなみに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の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次元表化、つまり縦並びテーブルを横並びテーブルに変換できますが、考え方は以下のとおりです。
- CASEでどのフィールドに値をセットするか選択。
- 他のフィールドが歯抜けになるのでGROUP化。
- 複数レコードの合計値ならば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でどの列に値をセットするか選択して、他の列が歯抜けにならないように集合関数で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から先の表現大変そう・・・・。
- 数学的に位取りができること
- 哲学的にない状態があるという事実を表現すること
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)