概要
| この記事の結論 Accessで2つのテーブルの一致レコードを抽出するには、共通の比較項目を内部結合（INNER JOIN）で結びます。クエリデザインでは結合プロパティで「両方のテーブルの結合フィールドが同じ行だけ」を選びます。相手側の項目が不要で存在確認だけをしたい場合はEXISTSも使えます。重複キーによる件数増加を確認してから結果を利用しましょう。 内部結合とは、指定した比較条件に一致するレコードの組み合わせを2つのテーブルから取り出す方法です。 |
この記事でわかること
- 比較するキーとデータ型をそろえる
- 一致する組み合わせはINNER JOIN
- 存在確認だけならEXISTSも選べる
- 結果件数と重複キーを検算する
この記事の主な内容
- 一致の意味と比較するキーを決める
- クエリデザインで一致レコードを抽出する
- INNER JOINとEXISTSをSQLで使い分ける
- 複数項目とNullを含む照合の考え方
- 抽出件数が増える原因と対処
- 不一致クエリの変更と実務での検算
- よくある質問
想定読了時間：約12分～16分
一致の意味と比較するキーを決める
2つのテーブルを照合するときは、最初に「何が同じなら一致とするか」を決めます。顧客IDが同じなのか、伝票番号と明細番号が両方同じなのか、すべての項目が一致するのかで、作るクエリは変わります。
同じIDの存在確認と全項目の比較を区別する
顧客IDが一致していても、住所や名称が一致するとは限りません。「顧客マスターに存在する売上を取得する」目的なら顧客IDの照合で足ります。一方、「移行前後のデータが同じか」を確認する場合は、比較対象の項目とNullなどの扱いまで決める必要があります。
主キーやフィールド名が同じである必要はない
内部結合は、対応するフィールドの値を比較して組み合わせを取り出す方法です。比較するフィールドが両方とも主キーである必要はなく、フィールド名も同じでなくて構いません。ただし、データ型の互換性と、値の意味が対応していることが必要です。[1]
親テーブルの主キーを子テーブルが参照する場合、子側の項目は一般に外部キーとして扱います。たとえば顧客IDは顧客テーブルで一意でも、売上テーブルには同じ顧客の複数売上が存在します。両方を無理に一意にすると、正当な複数売上を登録できなくなります。
| 確認項目 | 確認する理由 | 例 |
|---|---|---|
| 値の意味 | 同じ番号でも別体系なら比較できない | 社内顧客IDと取引先独自コード |
| データ型 | 数値と文字列の混在は照合の原因になる | 数値101と文字列の00101 |
| 前ゼロ | 数値変換で識別情報を失う場合がある | 商品コード00123 |
| Nullと空文字 | 未設定と文字数0は別の状態 | Null、空文字、空白文字 |
| 一意性 | 相手側の重複で結果が増える | 同じ顧客IDが2件 |
練習用データを用意する
ここでは、顧客IDが顧客マスターに存在する売上だけを取得します。下記は架空の例です。T_売上の売上IDを主キー、M_顧客の顧客IDを主キーとし、両表の顧客IDは同じ数値型の長整数型にそろえます。
| T_売上の売上ID | 顧客ID | 金額 |
|---|---|---|
| 1 | 101 | 1000 |
| 2 | 102 | 2000 |
| 3 | 101 | 3000 |
| 4 | 999 | 4000 |
| 5 | Null | 5000 |
| M_顧客の顧客ID | 顧客名 |
|---|---|
| 101 | あさひ商店 |
| 102 | みどり工業 |
| 103 | さくら企画 |
この例の期待結果は売上ID1・2・3の3件です。顧客999はマスターに存在せず、Nullは比較値がないため、通常の等値結合では一致しません。練習用に不整合を含めているため、この状態のまま参照整合性が有効な親子関係を作る想定ではありません。
クエリデザインで一致レコードを抽出する
一致する組み合わせを取得するには内部結合を使います。不一致クエリを一度作らなくても、クエリデザインで直接指定できます。リレーションシップ画面で参照整合性を設定する操作とは目的が異なります。[1]
| 手順 | 操作 | 確認ポイント |
|---|---|---|
| 1 | ［作成］→［クエリデザイン］ | 新しい選択クエリを作る |
| 2 | T_売上とM_顧客を追加 | 照合する2表が表示される |
| 3 | 顧客ID同士をドラッグして結ぶ | 比較項目の組み合わせが正しい |
| 4 | 結合線をダブルクリック | 結合プロパティを開く |
| 5 | 両方の結合フィールドが同じ行だけを含める設定を選ぶ | 内部結合になる |
| 6 | 売上ID・顧客ID・顧客名・金額を追加し実行 | 期待する3件が表示される |
自動でできた結合線も確認する
すでに関係が定義されている場合など、テーブル追加時に結合線が表示されることがあります。線があるだけで正しいとは判断せず、比較するフィールドと結合の種類を確認します。不要な結合線があると、意図しない追加条件で件数が減る場合があります。
出力する列の出所を決める
同名の顧客IDが2表にあるため、どちらの値を表示するか明確にします。今回は売上側の顧客IDと、マスター側の顧客名を選びます。クエリを「Q_売上一致」として保存し、売上IDの昇順で確認すると練習データと照合しやすくなります。
| 売上ID | 顧客ID | 顧客名 | 金額 |
|---|---|---|---|
| 1 | 101 | あさひ商店 | 1000 |
| 2 | 102 | みどり工業 | 2000 |
| 3 | 101 | あさひ商店 | 3000 |
INNER JOINとEXISTSをSQLで使い分ける
相手側の顧客名なども一緒に出すならINNER JOINが分かりやすい方法です。存在するかだけを条件にして売上側を取得したい場合は、EXISTSも選べます。必要なのが「組み合わせ」か「元データの該当レコード」かを先に決めます。[1][2]
相手側の項目も取得するINNER JOIN
クエリをSQLビューへ切り替えると、内部結合の設定を文字で確認できます。SとCはテーブルの別名です。以下は前節の練習データを対象にした選択クエリで、元のデータを更新・削除しません。
SELECT S.[売上ID], S.[顧客ID], C.[顧客名], S.[金額]
FROM [T_売上] AS S
INNER JOIN [M_顧客] AS C
ON S.[顧客ID] = C.[顧客ID]
ORDER BY S.[売上ID];
SELECTは表示する列、FROMは元になる表、ONは一致と判断する条件です。結合したという理由だけで、相手テーブルのすべての列が出力されるわけではありません。必要な列をSELECTへ明記します。[4]
相手の存在だけ確認するEXISTS
売上情報だけを取得し、顧客名を表示しなくてよい場合は次の書き方ができます。相手側に条件に合う行が1件でも存在するかを判定します。存在判定なので、相手側の同じキーが複数行あることによって外側の行を増やしません。
SELECT S.[売上ID], S.[顧客ID], S.[金額]
FROM [T_売上] AS S
WHERE EXISTS
(SELECT * FROM [M_顧客] AS C
WHERE C.[顧客ID] = S.[顧客ID])
ORDER BY S.[売上ID];
ただし、T_売上そのものに複数のレコードがあれば、それらはそれぞれ返ります。EXISTSは元テーブル全体の重複を取り除く機能ではありません。また、常にINNER JOINより高速になると決めつけず、データ量やインデックス、接続方式に応じて確認します。
複数項目とNullを含む照合の考え方
1項目では対象を識別できない場合は、複数項目をANDで結びます。例えば「顧客IDと伝票番号が同じときに一致」とするなら、片方だけが合ったレコードを取得しないよう両条件を指定します。
複合条件は1つずつ対応させる
次は、同じ構造で顧客IDと伝票番号を持つT_照合元とT_照合先の例です。前節のT_売上には伝票番号を用意していないため、そのまま同じテーブル名で実行する例ではありません。比較対象の2表を別に用意するか、実際のフィールド名へ置き換えます。
SELECT A.*
FROM [T_照合元] AS A
INNER JOIN [T_照合先] AS B
ON (A.[顧客ID] = B.[顧客ID])
AND (A.[伝票番号] = B.[伝票番号]);
ANDは両方の条件が成立する場合、ORはどちらか一方でも成立する場合です。同じ伝票の照合でORを使うと、顧客だけが同じ別伝票まで含む可能性があります。項目をつなげた文字列で比較すると区切り方による誤一致が起きるため、原則として項目ごとに対応させます。
Null同士は通常の等号では一致しない
Nullは値が未設定の状態です。「Null = Null」が真になると考えてはいけません。空文字列や0も別の値です。空欄の見た目が似ていても、実際に何が保存されているかを確認します。[6]
両側がNullのときも一致とする特殊な要件なら、対象項目について「両方がNull」の条件を明示します。ただし、識別用のキーでは未設定同士を同じ相手と判断してよいとは限りません。Nullをすべて0に置き換える方法は、実際の0と誤って一致させるおそれがあります。
前ゼロや表記揺れは元データの規則から直す
コード00123と123を同一視してよいかは、業務上のコード規則によります。文字列を機械的に数値へ変換すると、前ゼロの意味を失う場合があります。日付/時刻型も、画面では同じ日付に見えて内部の時刻が違えば等値比較で一致しないことがあります。
照合時にすべてのキーへ変換関数を掛けるより、取込時に形式をそろえた照合用項目を用意する方が管理しやすい場合があります。元データを残し、どの規則で空白や文字種をそろえたか記録してください。Accessの文字比較を、厳密なバイト単位の一致検査と同じものとして扱わないことも大切です。
抽出件数が増える原因と対処
内部結合は、条件が一致するすべての組み合わせを返します。片方に同じキーが2件、もう片方に3件あれば、そのキーについて2×3＝6件の組み合わせになります。件数が増えたからといって、元テーブルに新しい行が追加されたわけではありません。
顧客ごとの売上が複数あるのは正常な場合もある
練習例で顧客101の売上が2件あることは、異なる売上IDの取引が2件あるという意味です。顧客ごとに1件へ減らすのが目的なら集計が必要ですが、売上の照合なら2件とも必要です。正当な明細を重複と誤解して削除しないでください。
重複キーを調べる
次のクエリは、マスター側の顧客IDが2件以上あるかを調べます。顧客IDが主キーとして正しく設定された練習例では、重複は発生しません。CSV取込直後の作業テーブルなど、一意制約がない表を調べる場面を想定しています。
SELECT [顧客ID], Count(*) AS [件数]
FROM [M_顧客]
GROUP BY [顧客ID]
HAVING Count(*) > 1;
DISTINCTで見かけだけ合わせない
DISTINCTは、選択した列の組み合わせが同じ行をまとめます。表示列から売上IDを外すと、本来別の取引まで同じ値として1行になる場合があります。逆に顧客名などが異なれば、顧客IDが同じでも行は残ります。[5]
| 目的 | 検討する方法 | 注意点 |
|---|---|---|
| 相手の存在だけ確認 | EXISTS | 相手側の詳細列は取得しない |
| 相手の列も表示 | INNER JOIN | 相手キーの一意性を確認 |
| 取引を顧客単位にまとめる | 適切なGROUP BYと集計 | 取引単位の結果とは異なる |
| 誤登録を修正 | 原因調査後にデータ整備 | 結果の行数合わせだけで削除しない |
件数の合わないAccessクエリの調査が必要な場合は
まずは本記事の手順をコピー上で試し、設定と保存先を確認してください。既存VBAや複数の画面、外部データへの影響が分からない場合は、変更範囲を調査してから改修する方法が適しています。
Accessの教科書ではAccessの開発・保守・改修やSQL Serverとの連携を扱っています。ご相談時には、発生する画面、操作手順、エラー内容、利用環境を整理すると状況を共有しやすくなります。
不一致クエリの変更と実務での検算
不一致クエリウィザードから作る方法もあります。その場合は、どちら側を残す外部結合か、どちらの列でNullを判定しているかを確認します。単に画面に見えるIs Nullをすべて変更する手順にはしません。[3]
判定するのは相手側のキー
次は売上をすべて残すLEFT JOINに対し、顧客マスター側のキーが存在する行だけを残す例です。この例ではM_顧客の顧客IDは主キーでNullにならないため、一致の判定列として使えます。
SELECT S.[売上ID], S.[顧客ID], C.[顧客名], S.[金額]
FROM [T_売上] AS S
LEFT JOIN [M_顧客] AS C
ON S.[顧客ID] = C.[顧客ID]
WHERE C.[顧客ID] Is Not Null;
これをIs Nullにすると、相手が見つからない売上を取得できます。判定列をS.顧客IDにすると、売上側に値はあるがマスターには存在しない999などを正しく区別できません。また、相手側の顧客名が任意入力の場合、名前がNullであることだけを不一致と判断するのも不適切です。
元の行単位で一致と不一致を検算する
練習データでは元の売上5件に対し、一致した売上IDが3件、不一致が2件です。不一致の内訳は999の1件とNullの1件です。一致3件の金額合計は6000、不一致2件は9000で、元の合計15000と一致します。
| 確認項目 | 練習例の期待値 | 確認する意味 |
|---|---|---|
| 元の売上件数 | 5件 | 比較の母数 |
| 一致した売上IDの件数 | 3件 | 元レコード単位で該当 |
| 不一致の売上IDの件数 | 2件 | 未登録キーとNullを含む |
| 一致＋不一致 | 5件 | 元データを過不足なく分けた |
| 金額の検算 | 6000＋9000＝15000 | 二重計上や漏れを確認 |
相手に重複キーがある場合、JOIN結果の行数は元の一致件数と同じとは限りません。そのときはEXISTSで元レコードを分類するか、売上IDなどの一意な識別子で数え直します。検算中に元データが更新されると結果も変わるため、同じ時点のコピーで比較してください。
性能とデータ更新は別に検討する
大量データでは比較キーのインデックスが候補です。ただし、型変換を含む条件、複雑な結合、外部接続によって実行方法が変わるため、実際のデータ量で計測します。小さなサンプルで正しい結果を確認してから性能を調整すると、誤った高速化を避けられます。
本記事のクエリは抽出用です。一致したデータを更新・削除する場合は、別の作業として対象件数とバックアップを確認します。まず正しい比較キーを選び、必要な結果単位を決め、件数を検算することが一致抽出の基本です。
よくある質問
必須ではありません。ただし、どちらのキーが一意かによって結果件数が変わります。参照整合性を設定することと、クエリの結合条件を指定することも区別します。
できます。値の意味とデータ型が対応していれば、異なる名前のフィールドをON条件で結べます。名前が同じでも別の意味なら比較対象にはできません。
型の違い、前ゼロ、Null、空白、日付の時刻部分、追加の結合条件などを確認します。画面上の見た目だけで同一と判断しないでください。
同じキーに対して複数の組み合わせができるためです。2件と3件を結ぶと6件になります。相手の存在だけを確認する目的ならEXISTSを検討します。
目的が異なります。重複クエリは通常、ある表やクエリ内で値が重複するレコードを探します。2表の一致は内部結合などで比較条件を明示します。
ここで紹介したSELECTによる抽出を実行するだけでは更新されません。ただし、結果のデータシートを手で編集できるクエリもあるため、閲覧時の編集操作とは区別してください。
件数の合わないAccessクエリの調査が必要な場合は
まずは本記事の手順をコピー上で試し、設定と保存先を確認してください。既存VBAや複数の画面、外部データへの影響が分からない場合は、変更範囲を調査してから改修する方法が適しています。
Accessの教科書ではAccessの開発・保守・改修やSQL Serverとの連携を扱っています。ご相談時には、発生する画面、操作手順、エラー内容、利用環境を整理すると状況を共有しやすくなります。
[2] SQLサブクエリとEXISTS
https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/sql-subqueries-microsoft-access-sql
[5] 固有の値
https://support.microsoft.com/en-us/access/uniquevalues-property
[6] IsNull
https://support.microsoft.com/en-us/access/isnull-function
[7] Count
https://support.microsoft.com/en-us/access/count-function
関連記事
Accessのクエリとは？種類・抽出条件・作り方を解説｜Accessの教科書
Accessの主キーとは 設定と重複対策｜Accessの教科書