外部結合とは？

外部結合の意味

結合条件に合う相手がない行も結果に残し、相手側の列をNULLで埋める結合。残す側により左・右・完全の3種類がある。

外部結合の具体例

受注がまだ1件もない顧客も含めて顧客一覧を出したいとき、顧客を左に置いた左外部結合を使う。両側の非対応行を残したいときは完全外部結合。

外部結合は試験でどう引っ掛けられる？

相手側の絞り込み条件をWHERE句に書くと、NULLで埋まった行が条件に合わず除去され、実質内部結合と同じ結果になる。外側表を残したい絞り込みはON句に書く。

外部結合と関連する用語

• 3値論理

外部結合が出た過去問

• ある月の“月末商品在庫”表と“当月商品出荷実績”表を使って、ビュー“商品別出荷実績”を定義した。このビューにSQL文を実行した結果の値はどれか。 月末商品在庫：…

  正解：400

  要点：外部結合で生じたNULLはWHERE条件では選ばれない

  ビューは左外部結合で作られているため、出荷実績のない商品は出荷実績数がNULLになる。WHERE句の条件はNULLに対してunknownとなり行が選ばれないので、対象は出荷実績数150の商品と300の商品の2件だけとなる。それぞれの月末在庫数100と300を合計して400になる。

  出典：平成29年度 春期 データベーススペシャリスト試験 am2 問10（IPA）

• “部品”表から、部品名に'N11'が含まれる部品情報（部品番号、部品名）を検索するSQL文がある。このSQL文は、検索対象の部品情報のほか、対象部品に親部品番号…

  正解：B1 LEFT OUTER JOIN 部品 B2

  要点：対応が無い行も残したい側を左に置いた左外部結合を使う

  検索条件に合う部品は必ず結果に残しつつ、親部品が設定されていない場合は親側の列をNULLで返したい。この動作は、条件に合う側を残す左外部結合そのものである。内部結合では親部品を持たない部品が結果から消えてしまう。

  出典：平成30年度 春期 データベーススペシャリスト試験 am2 問8（IPA）

• “社員取得資格”表に対し、SQL文を実行して結果を得た。SQL文のaに入れる字句はどれか。 〔社員取得資格〕 社員コード, 資格 S001, FE S001, …

  正解：ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C1.資格 = 'FE'

  要点：外部結合の右側条件はON句、左側の絞り込みはWHERE句に置く

  結果には基本情報の資格を持つ社員だけが並び、応用情報を持たない社員は資格2がNULLになっている。左外部結合で残す側の絞り込み（基本情報を持つ行）はWHERE句で行い、応用情報の有無による突合せはON句に置くことで、対応が無い場合もNULLで残せる。

  出典：令和1年度 春期 データベーススペシャリスト試験 am2 問11（IPA）

• "社員取得資格"表に対し、SQL文を実行して結果を得た。SQL文のaに入れる字句はどれか。 社員取得資格(社員コード,資格): S001,FE / S001,A…

  正解：ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C1.資格 = 'FE'

  要点：外部結合の絞り込みはON句へ。WHERE句だと内部結合化する

  同じ表を左外部結合し、左側にFEの行、右側に同じ社員のAPの行を対応付ける形にすればよい。結合条件をON句に書けば対応しない行も左側は残り、右側はNULLになる。そのうえでWHERE句で左側をFEの行に限定すれば、FE保有者ごとにAPの有無を並べた結果が得られる。よってアが正しい。

  出典：令和3年度 秋期 データベーススペシャリスト試験 am2 問8（IPA）

• "文書"表、"社員"表から結果を得るSQL文のaに入れる字句はどれか。

  正解：文書 LEFT OUTER JOIN 社員 A ON 文書.作成者ID = A.社員ID LEFT OUTER JOIN 社員 B ON 文書.承認者ID = B.社員ID

  要点：残したい側を左に置いた左外部結合を別名で繰り返す

  結果には、社員表に対応する社員がいない作成者IDの行も残り、その氏名がNULLになっている。文書表の行をすべて残す必要があるので、文書表を左側に置いた左外部結合を、作成者用と承認者用の別名で2回続けて行えばよい。これを表しているのがアである。

  出典：令和4年度 秋期 データベーススペシャリスト試験 am2 問6（IPA）