SQLの応用（結合・副問合せ・データ更新）

このページでは、内結合と外結合、副問合せとEXISTS、WITH句とCASE式、INSERT・UPDATE・DELETEと参照動作がわかります。

一言で言うと：
- 関係データベースを操作する標準言語SQLの話。中心は、データを取り出すSELECT文
- 条件の書き方、グループ化、表の結合（内結合・外結合）、副問合せ、WITH句・CASE式、データの追加・変更・削除と、参照制約がある表の更新まで出てくる

SQL文の種類 → SELECTの骨組み（FROM→WHERE→GROUP BY→HAVING→SELECTの順）→ 条件・集計・並べ替え → 表をつなぐ（内結合、外結合、COALESCE、相関名）→ 問合せの中の問合せ（副問合せ、EXISTS）→ 一時表と場合分け（WITH句、CASE式）→ 書き換える（INSERT、UPDATE、DELETE、参照動作）

覚え方の軸
表の結合：どちらを基準に残すか（★）
副問合せ：問合せの中の問合せ（★）
SQLによる集合演算の実現
自然結合（NATURAL JOIN）
WITH句とCASE式：一時表と場合分け（★）
データ更新：追加・変更・削除と参照動作（★）
混同しやすいペア

覚え方の軸

結合は「どちらを基準に残すか」で分ける
- 内結合は、両方にある行だけ
- 左外結合は左の表を全部、右外結合は右の表を全部、完全外結合は両方全部残し、相手がなければNULLで埋める
副問合せは「1つの値ならイコール、複数ならIN、あるかないかならEXISTS」
- 主問合せの行を1行ずつもらって動くのが相関副問合せ
参照動作は「消せない（NO ACTION・RESTRICT）、連鎖（CASCADE）、置き換え（SET DEFAULT・SET NULL）」
- 省略時はNO ACTION。試験に出るのはCASCADEとSET DEFAULT

表の結合：どちらを基準に残すか（★）

この項の内容：
- 「SELECT…FROM…WHERE」を使う従来型の結合（等結合）以外の結合演算

① 内結合（INNER JOIN）

内結合：
- 等結合と同じ結果が得られる演算
- 従来の等結合は、結合する表をFROM句、結合条件をWHERE句で指定する
- 内結合は、結合する表をFROM句の中でJOINを使って指定し、結合条件はJOINに続くON句で指定する
前の等結合の例と同じ結果になるSQL：

SELECT 社員.社員コード, 社員.社員名, 部門.部門コード, 部門.部門名
  FROM 社員 INNER JOIN 部門
           ON 社員.部門コード = 部門.部門コード

例：社員表と部門表

表	行
社員表	（1101、青木花子、S1）、（1201、石井健、S2）、（2201、上田翔、H1）
部門表	（S1、販売1課）、（S2、販売2課）、（S3、販売3課）

内結合の結果：
- （1101、青木花子、S1、販売1課）、（1201、石井健、S2、販売2課）
- 結合する両方の表に存在する行だけを取り出す（H1の上田翔と、S3の販売3課は出てこない）
自然結合でも書ける：
- 結合列の列名が2つの表で同じ場合は、自然結合（NATURAL JOIN）でも実現できる
データの補足：
- 社員表の部門コードが部門表の部門コードを参照する外部キーなら、部門コード’H1’は本来登録できない。ここでは説明のため、あえてこのデータにしている

② 外結合（OUTER JOIN）

外結合：
- 等価結合や内結合は、結合列の値が等しい行だけを取り出す
- これに対し、結合列の値が一致しない行も結果に反映できるのが外結合
- 結合相手の表に該当する行がない場合は、それをNULL（空値）で埋めて結合する
- どちらの表を基準にするかで、3つある

外結合型（★）

種類	中身
左外結合（LEFT OUTER JOIN）	JOINの前（左）に書いた表を基準にして、JOINの後（右）の表に存在しない行をNULLで埋めて結合する
右外結合（RIGHT OUTER JOIN）	JOINの後（右）に書いた表を基準にして、JOINの前（左）の表に存在しない行をNULLで埋めて結合する
完全外結合（FULL OUTER JOIN）	左外結合と右外結合を組み合わせた演算。片方にだけ存在する場合、もう片方をNULLで埋めて結合する

3つの外結合の結果

書き方：
- 「FROM 社員 LEFT OUTER JOIN 部門 ON 社員.部門コード = 部門.部門コード」（RIGHT、FULLも同じ形）
左外結合の結果（3行）：
- 青木花子（S1、販売1課）、石井健（S2、販売2課）、上田翔（NULL、NULL）
右外結合の結果（3行）：
- 青木花子（S1、販売1課）、石井健（S2、販売2課）、NULL（社員なし）（S3、販売3課）
完全外結合の結果（4行）：
- 青木花子、石井健、上田翔（NULL、NULL）、NULL（S3、販売3課）
OUTERは省略できる：
- どの外結合も、OUTERは省略可能（LEFT JOINなど）
基準表は全部残る：
- 外結合では、ON句に指定した条件にかかわらず、基準表のすべての行が結果表に反映される
外結合の例え：
- 「クラス名簿」と「部活名簿」を突き合わせる。内結合は、部活に入っている生徒だけ。左外結合（クラス名簿が基準）は、帰宅部の生徒も「部活：空欄」で載る。右外結合（部活名簿が基準）は、部員がいない部活も「生徒：空欄」で載る。完全外結合は、その両方

例題（結合の行数）

問題：
- 上の社員表（3行）と部門表（3行）で、内結合、左外結合、右外結合、完全外結合、直積（CROSS JOIN）の結果は、それぞれ何行になるか
答え：
- 内結合は、部門コードが一致する青木・石井の 2行
- 左外結合は、社員3人が全部残るので 3行
- 右外結合は、部門3つが全部残るので 3行
- 完全外結合は、一致する2行＋社員側だけの1行（上田）＋部門側だけの1行（販売3課）＝ 4行
- 直積は、3 × 3 ＝ 9行
検算：
- 完全外結合＝左外結合＋右外結合 − 内結合＝ 3 ＋ 3 − 2 ＝ 4

③ COALESCE式

完全外結合の問題：
- 上の完全外結合の結果の部門コードは、部門表の部門コード
- だから、社員コード2201の上田翔の部門コード（H1）が、結果に出てこない（NULLになる）
COALESCE式：
- COALESCE(A, B)は、AがNULLでなければAを、AがNULLでBがNULLでなければBを返す。BもNULLならNULLを返す
- つまり、左から順に見て、最初のNULLでない値を返す
使い方：
- 部門表の部門コードがNULLなら、社員表の部門コードを出すようにする

SELECT 社員.社員コード, 社員.社員名,
       COALESCE(部門.部門コード, 社員.部門コード), 部門.部門名
  FROM 社員 FULL OUTER JOIN 部門
         ON 社員.部門コード = 部門.部門コード

結果：
- 上田翔の行の部門コードが、NULLではなくH1になる
COALESCEは関数と考えてよい：
- 1つの値を返すので、関数ととらえてかまわない
- 括弧の中には、複数の値式を書ける
COALESCEの例え：
- 「連絡先：携帯、なければ自宅、なければ会社」。左から順に見て、最初に空欄でないものを使う

④ 表に相関名（別名）を設定する

相関名：
- 「表名 AS 相関名」と書くと、表に相関名（別名）を付けられる
- ASは省略でき、通常は省略して書く
試験での形式：
- 試験では、この形式で出題されるので慣れておく
- 例えば、社員表にA、部門表にBという相関名を付けると、次のようになる

SELECT A.社員コード, A.社員名,
       COALESCE(B.部門コード, A.部門コード), B.部門名
  FROM 社員 A FULL OUTER JOIN 部門 B
         ON A.部門コード = B.部門コード

相関名を付けた後の注意：
- 相関名を付けると、以降、その表名は使えない
- 列名は「相関名.列名」で指定する必要があり、「表名.列名」で書くと誤り
自己結合：
- 表に相関名を付けると、同じ表どうしを結合できる。これを自己結合という
- その表の中のある情報を、同じ表の中の他の情報と照合するときに使う
- 例えば、社員表に「上司コード」を追加したとき、社員表を自己結合すれば、ある社員の上司の社員名を得られる
自己結合の例え：
- 同じ名簿を2部コピーして、1部目の「上司の番号」を、2部目の「社員番号」と突き合わせる

⑤ 3つの表を結合する

例：
- 社員表、部門表に加え、売上表（部門コード、売上：S1 1,000、S2 2,000、S3 3,000）を使って、「社員コード1101の社員（青木花子）の所属部門の売上」を求める
- 結果は、（青木花子、販売1課、1,000）

従来型等結合で書く場合

SELECT A.社員名, B.部門名 AS 所属部門, C.売上 AS 所属部門の売上
  FROM 社員 A, 部門 B, 売上 C
  WHERE A.部門コード = B.部門コード
    AND B.部門コード = C.部門コード
    AND A.社員コード = 1101

内結合で書く場合

SELECT A.社員名, B.部門名 AS 所属部門, C.売上 AS 所属部門の売上
  FROM 社員 A
    INNER JOIN 部門 B ON A.部門コード = B.部門コード
    INNER JOIN 売上 C ON B.部門コード = C.部門コード
  WHERE A.社員コード = 1101

内結合の動き：
- ①社員表（A）と部門表（B）を、部門コードで結合
- ②①で得られた結果表と売上表（C）を、部門コードで結合
JOINの順番：
- FROM句に書いた順にJOINが実行される（A JOIN B の結果に、C をJOIN）

副問合せ：問合せの中の問合せ（★）

副問合せ：
- SQL文の中に現れる、括弧で囲んだ問合せ文（SELECT）
主問合せ：
- 副問合せを含む、外側の問合せ
出題の傾向（試験）：
- 近年の試験では、WHERE句やHAVING句はもちろん、FROM句やSELECT句にも副問合せを使ったSQL文が出る

① 副問合せの使用例1（FROM句で絞り込む）

FROM句の副問合せ：
- FROM句に副問合せを書くと、処理対象のデータを絞り込める
- 副問合せから導出される表が、主問合せの処理対象表になる
同値な2つのSQL文：
- 「SELECT 社員名 FROM (SELECT ＊ FROM 社員 WHERE 部門コード = ‘S1’)」
- 「SELECT 社員名 FROM 社員 WHERE 部門コード = ‘S1’」
副問合せに相関名を付ける：
- 「FROM (SELECT ＊ FROM 社員 WHERE 部門コード = ‘S1’) AS t1」のように書く

② 副問合せの使用例2（比較演算子と組み合わせる）

比較演算子と組み合わせる副問合せ：
- 単一値を導出する必要がある
エラーになる例：

SELECT 社員名
  FROM 社員
  WHERE 部門コード =
   (SELECT 部門コード FROM 部門 WHERE 部門名 LIKE '販売%')

エラーになる理由：
- 最初に副問合せが実行され、部門名が「販売」で始まる部門の部門コードが導出される
- 販売1課と販売2課があると、部門コードが2つ出てくる
- 1つならエラーにならないが、複数だと「＝」で比べられないのでエラー
解決策：
- 比較演算子「＝」の代わりにINを使う（「WHERE 部門コード IN (SELECT …)」）

③ 相関副問合せとEXISTS

相関副問合せ：
- 主問合せの1行ずつをもらって、順番に実行される副問合せ
相関副問合せの見分け方：
- 副問合せが、自分のFROM句で指定していない主問合せの表を使っているかどうかで判断する

例：表Aと表B

表	行
表A	（1101、青木花子、S1）、（1201、石井健、S2）
表B	（1101、青木花子、S1）、（2201、上田翔、H1）

SELECT * FROM A
  WHERE EXISTS
     (SELECT * FROM B WHERE A.社員コード = B.社員コード)

動き：
- ①主問合せの表Aから1行ずつ渡す
- ②副問合せを実行する
- ③結果を返す
- 副問合せのFROM句で表Aを指定していないのに、A.社員コードを使っているので、相関副問合せだとわかる
EXISTS：
- 副問合せからの導出値（結果値）があれば“真”、なければ“偽”と評価する演算子
- 上の例では、表Aの社員コード1101の行だけが“真”になる
- だから、表Aと表Bの両方に登録されている社員（1101 青木花子）が求められる
NOT EXISTS：
- 表Aに登録されていて、表Bには登録されていない社員が求められる（石井健）
EXISTSは有無だけを見る：
- EXISTSを使った場合、副問合せからの導出値そのものは意味を持たず、結果の有無だけが評価される
EXISTSで集合演算：
- 上のSQL文で、共通演算「表A∩表B」を実現できる
- NOT EXISTSにすれば、差演算「表A−表B」を実現できる
相関副問合せの例え：
- 名簿Aを上から1人ずつ読み上げ、そのたびに名簿Bをめくって「この人いる？」と探す

SQLによる集合演算の実現

演算	SQL文
和	SELECT ＊ FROM A UNION SELECT ＊ FROM B
共通	SELECT ＊ FROM A INTERSECT SELECT ＊ FROM B
差	SELECT ＊ FROM A EXCEPT SELECT ＊ FROM B
直積	SELECT ＊ FROM A CROSS JOIN B　または　SELECT ＊ FROM A, B

自然結合（NATURAL JOIN）

自然結合：
- 結合列の列名が2つの表で同じ場合に使える
- 例えば、「SELECT ＊ FROM 社員 NATURAL JOIN 部門」
自然結合の特徴：
- 同じ名前の列を自動的に結合列と判断して結合するので、結合条件を書く必要がない
自然結合の注意：
- 結果表には、どちらか一方の結合列しか残らない
- だから、結合列を「表名.列名」で指定するとエラーになる

WITH句とCASE式：一時表と場合分け（★）

① WITH句

WITH句：
- その問合せの中だけで使える、一時的な表を定義する
- 問合せの中で、同じ表の導出を何回も行わなければならない場合に有効
共通表式：
- WITH句に書いたもの
例：所属人数が最も多い部門の部門コードと所属人数を求める：

WITH 部門所属人数 (部門コード, 所属人数)
  AS (SELECT 部門コード, COUNT(*)
        FROM 社員 GROUP BY 部門コード)
SELECT 部門コード, 所属人数
  FROM 部門所属人数
  WHERE 所属人数 = (SELECT MAX(所属人数) FROM 部門所属人数)

読み方：
- 「部門所属人数」は、ASの後の括弧でくくった副問合せから導出された表に付けた名前（問合せ名）
- （部門コード, 所属人数）は、導出列名
- 主問合せでは、WITH句で定義した問合せ名「部門所属人数」を、2回使っている（FROM句と副問合せ）
WITH句の例え：
- 計算の途中で何度も使う値を、メモ用紙に「X＝…」と書いておき、その計算の中だけで使う

② CASE式

CASE式：
- 「〜ならば○○に、〜ならば△△に」というように、条件に応じて元の値を別の値に変換するときに使う
例：年齢で年代を求める：

SELECT 社員名, CASE
                 WHEN 年齢 < 20 THEN '20未満'
                 WHEN 年齢 >= 20 AND 年齢 < 30 THEN '20代'
                 ELSE '30以上'
               END AS 年代
  FROM 社員

結果（社員表は前に出てきたもの）：
- 青木花子（19歳）は「20未満」、青木進（22歳）・石井健（24歳）・小林誠（28歳）は「20代」、岡本恵（30歳）は「30以上」
CASE式を書ける場所：
- 値式が使える場所なら、どこでも使える
CASE式の例え：
- 成績表の「90点以上はA、70点以上はB、それ以外はC」という変換ルール

データ更新：追加・変更・削除と参照動作（★）

① INSERT文

INSERT文：
- 表に行を挿入する
INSERT文の構文：
- ①挿入する値をVALUES句で指定する：INSERT INTO 表名 [(列名リスト)] VALUES(値リスト)
- ②問合せ（SELECT）の結果をすべて挿入する：INSERT INTO 表名 [(列名リスト)] 問合せ（SELECT）
一部の列だけに挿入する場合：
- どの列に挿入するかを、列名リストに指定しなければならない
- 省略された列には、DEFAULT制約が指定されていればその既定値、なければNULLが入る
挿入先の表の注意：
- INTO句で指定した表（挿入先の表）は、問合せのSELECT文では使えない

② UPDATE文

UPDATE文：
- 表の中のデータを変更する
UPDATE文の構文：
- UPDATE 表名 SET 列名 = 変更値 [WHERE 条件]
SET句：
- 変えたい列の値を「列名 = 変更値」の形で指定する
- 1つのUPDATE文で複数の列を変えるときは、カンマで区切る
WHERE句：
- 省略すると、表の中のすべての行が変更される
- WHERE句を書くと、条件に合う行だけを変更できる
変更値に書けるもの：
- CASE式や、単一値を導出する副問合せも書ける
CASE式を使った例：

UPDATE 商品 SET
  販売価格 =
    CASE
      WHEN 販売ランク = 'A' THEN 単価*0.9
      WHEN 販売ランク = 'B' THEN 単価*0.8
      ELSE 単価
    END

読み方：
- 販売ランクが’A’なら単価×0.9、’B’なら単価×0.8、それ以外なら単価を、販売価格に設定する

③ DELETE文

DELETE文：
- 表の中の行を削除する
DELETE文の構文：
- DELETE FROM 表名 [WHERE 条件]
WHERE句：
- 省略すると、表の中のすべての行が削除される
- WHERE句を書くと、条件に合う行だけを削除できる
DELETEとDROP：
- 表の中の全行を削除しても、表自体は残る
- 表そのものを削除するのはDROP文

INSERT・UPDATE・DELETEの例

INSERT：
- 「INSERT INTO 社員 VALUES (1302, 中村翼, ‘S3’)」で、中村翼の行が追加される
UPDATE：
- 「UPDATE 社員 SET 部門コード = ‘S2’ WHERE 社員コード = 1302」で、中村翼の部門コードがS3からS2に変わる
DELETE：
- 「DELETE FROM 社員 WHERE 社員コード = 1302」で、中村翼の行が削除される
WHERE句の忘れに注意：
- UPDATEやDELETEでWHERE句を書き忘れると、全員が変わる・全員が消える

④ 参照関係を持つ表の更新

参照制約がある場合の制限：
- 関連する2つの表の間に参照制約が設定されていると、被参照表の主キー（候補キー）にない値を、参照表の外部キーに追加できない
- また、被参照表の行の削除や変更も、制約を受ける
参照制約：
- 外部キーの値が、被参照表の主キー、あるいは主キー以外の候補キーに存在することを保証する制約
- 2つの表の間に参照制約を設定する目的は、データの矛盾を起こすような行の追加や削除・変更を制約するため
参照動作指定：
- 被参照表の行を削除・変更するとき、どんな制約（動作）にするかは、明示的に指定できる。これを参照動作指定という
- CREATE TABLE文のREFERENCES句（参照指定）の後に、次の構文で指定する

REFERENCES 被参照表(参照する列リスト)
       [ON DELETE 参照動作]
       [ON UPDATE 参照動作]

省略時の既定値：
- 参照動作指定を省略した場合は、NO ACTION

参照動作（★）

参照動作	中身
NO ACTION	削除（変更）を実行するが、それで参照制約が満たされなくなった（参照行が残っていた）場合は実行失敗となり、削除（変更）処理は取り消される。つまり、その行を参照している参照表の行があれば、削除（変更）はできない
RESTRICT	削除（変更）を実行する際に参照制約を検査し、その行を参照している参照表の行があれば、削除（変更）を拒否する。つまり、削除（変更）はできない
CASCADE	削除（変更）を実行し、さらに、その行を参照している参照表の行があれば、その行も削除（変更）する。つまり、削除（変更）の動作が連鎖する
SET DEFAULT	被参照表の行が削除（変更）されたとき、それを参照している参照表の外部キーへ既定値を設定する
SET NULL	被参照表の行が削除（変更）されたとき、それを参照している参照表の外部キーへNULLを設定する

出題傾向（試験）：
- 試験では、“CASCADE”と“SET DEFAULT”が出題される
NO ACTIONとRESTRICTの違い：
- 結果はどちらも「消せない」
- NO ACTIONは、いったん実行してみて、最後に矛盾があれば取り消す
- RESTRICTは、実行する前に検査して、すぐ拒否する

追加と削除の制約

例の表：
- 部門表（S1 販売1課、S2 販売2課）
- 社員表（1101 青木花子 S1、1102 青木進 S2、1301 岡本恵 S2）
追加の制約：
- 社員表に（2201、上田翔、H1）を追加しようとしても、部門表に参照すべき部門コードH1がないので、追加できない
削除の制約（部門表のS1を消すとき）：
- RESTRICT指定・NO ACTION指定なら、社員表から参照されているので削除できない
- CASCADE指定なら、社員表の“S1”を持つ行（青木花子）も同時に削除する
参照動作の例え：
- 部署を廃止するとき、NO ACTION・RESTRICTは「まだ所属している人がいるので廃止できません」、CASCADEは「所属している人ごと名簿から消す」、SET DEFAULTは「所属を『未配属』にする」、SET NULLは「所属を空欄にする」
データの整合性を保つ制約：
- 一意性制約、参照制約のほか、データ項目のデータ型や桁数に関する形式制約、データ項目が取りうる値の範囲に関するドメイン制約がある

混同しやすいペア

等結合（従来型）／内結合（INNER JOIN）
- 従来型は、FROM句に表をカンマで並べ、WHERE句に結合条件を書く
- 内結合は、FROM句でJOINを使い、ON句に結合条件を書く（結果は同じ）
内結合／外結合
- 内結合は、両方の表に存在する行だけ
- 外結合は、一致しない行もNULLで埋めて残す
左外結合／右外結合／完全外結合
- 左外結合は、JOINの左の表を基準に全行残す
- 右外結合は、JOINの右の表を基準に全行残す
- 完全外結合は、両方の表の全行を残す
自然結合（NATURAL JOIN）／内結合（INNER JOIN）
- 自然結合は、同じ名前の列を自動で結合列にする。結合条件は不要。結合列は1つだけ残る
- 内結合は、ON句で結合条件を書く
COALESCE ／ CASE
- COALESCEは、左から順に見て最初のNULLでない値を返す
- CASEは、条件に応じて値を変換する
相関名／表名
- 相関名は、表に付けた別名。付けた後は「相関名.列名」で書く
- 相関名を付けた後に「表名.列名」で書くと誤り
自己結合／通常の結合
- 自己結合は、相関名を使って同じ表どうしを結合する（上司の名前を得るなど）
- 通常の結合は、別々の表を結合する
副問合せ／相関副問合せ
- 副問合せは、最初に1回だけ実行され、その結果を主問合せが使う
- 相関副問合せは、主問合せの1行ずつをもらって、何回も実行される
「＝ (副問合せ)」／「IN (副問合せ)」
- 「＝」は、副問合せの結果が1つの値のときだけ使える（複数だとエラー）
- 「IN」は、副問合せの結果が複数でも使える
EXISTS ／ NOT EXISTS
- EXISTSは、副問合せの結果があれば真（共通演算を実現できる）
- NOT EXISTSは、副問合せの結果がなければ真（差演算を実現できる）
WITH句／ビュー
- WITH句は、その問合せの中だけで使える一時的な表（共通表式）
- ビューは、CREATE VIEWで定義し、ずっと使える仮想表
INSERT（VALUES）／ INSERT（SELECT）
- VALUES句は、挿入する値を直接書く
- SELECTは、問合せの結果をすべて挿入する
NO ACTION ／ RESTRICT
- NO ACTIONは、実行した後、矛盾があれば取り消す（省略時の既定値）
- RESTRICTは、実行前に検査して拒否する
CASCADE ／ SET DEFAULT ／ SET NULL
- CASCADEは、参照している行も一緒に削除（変更）する（連鎖する）
- SET DEFAULTは、参照している行の外部キーを既定値にする
- SET NULLは、参照している行の外部キーをNULLにする
形式制約／ドメイン制約
- 形式制約は、データ型や桁数に関する制約
- ドメイン制約は、取りうる値の範囲に関する制約

← 06 データベースの目次に戻る