データベースにおける複数テーブル連携クエリの実践的解説

1. 複数テーブルを跨るデータ検索の基礎

1.1 概要と準備

1.1.1 サンプル環境構築

実践的な理解のため、以下のDDLとDMLでテスト環境を構築します。

-- 専用データベースの作成を推奨
CREATE DATABASE join_query_practice;
USE join_query_practice;

-- 部署マスタ
CREATE TABLE division_master (
    division_code INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '部署コード',
    division_name VARCHAR(20) NOT NULL UNIQUE COMMENT '部署名',
    created_at DATETIME NOT NULL COMMENT '作成日時',
    updated_at DATETIME NOT NULL COMMENT '更新日時'
) COMMENT='部署マスタ';

-- 部署データの初期投入
INSERT INTO division_master (division_code, division_name, created_at, updated_at) VALUES
(1, '総務部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, '技術部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'カスタマーサポート', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, '営業部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, '人事部', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- 従業員テーブル
CREATE TABLE employee_data (
    employee_code INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '従業員コード',
    account_name VARCHAR(25) NOT NULL UNIQUE COMMENT 'アカウント名',
    account_pass VARCHAR(40) DEFAULT 'default123' COMMENT 'パスワードハッシュ',
    display_name VARCHAR(20) NOT NULL COMMENT '表示名',
    gender_flag TINYINT UNSIGNED NOT NULL COMMENT '性別フラグ（1:男性 2:女性）',
    profile_image VARCHAR(400) COMMENT 'プロフィール画像パス',
    job_grade TINYINT UNSIGNED COMMENT '職位グレード',
    entry_date DATE COMMENT '入社日',
    division_code INT UNSIGNED COMMENT '所属部署コード',
    created_at DATETIME NOT NULL COMMENT '作成日時',
    updated_at DATETIME NOT NULL COMMENT '更新日時'
) COMMENT='従業員データ';

-- 従業員データの初期投入（17件）
INSERT INTO employee_data (employee_code, account_name, account_pass, display_name, gender_flag, profile_image, job_grade, entry_date, division_code, created_at, updated_at) VALUES
(1, 't.yamada', 'hash001', '山田太郎', 1, 'avatar/001.png', 3, '2010-04-01', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'h.suzuki', 'hash002', '鈴木花子', 2, 'avatar/002.png', 2, '2015-07-15', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'k.tanaka', 'hash003', '田中健一', 1, 'avatar/003.png', 2, '2018-03-20', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'm.watanabe', 'hash004', '渡辺美咲', 2, 'avatar/004.png', 1, '2020-09-01', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 't.nakamura', 'hash005', '中村哲也', 1, 'avatar/005.png', 1, '2019-11-11', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 'h.ito', 'hash006', '伊藤弘子', 2, 'avatar/006.png', 2, '2015-05-10', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 'r.kobayashi', 'hash007', '小林涼', 1, 'avatar/007.png', 1, '2021-02-14', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 's.saito', 'hash008', '斎藤聡子', 2, 'avatar/008.png', 1, '2022-06-30', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 'y.kato', 'hash009', '加藤雄大', 1, 'avatar/009.png', 1, '2017-12-25', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'a.yoshida', 'hash010', '吉田亜美', 2, 'avatar/010.png', 1, '2023-01-08', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(11, 'k.yamaguchi', 'hash011', '山口健太', 1, 'avatar/011.png', 3, '2013-08-05', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 'm.matsumoto', 'hash012', '松本麻衣', 2, 'avatar/012.png', 3, '2014-10-20', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(13, 't.inoue', 'hash013', '井上太郎', 1, 'avatar/013.png', 3, '2011-06-18', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(14, 's.kimura', 'hash014', '木村翔', 1, 'avatar/014.png', 2, '2012-12-12', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(15, 'n.shimizu', 'hash015', '清水奈々', 2, 'avatar/015.png', 2, '2014-04-28', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(16, 'd.saito', 'hash016', '斎藤大輔', 1, 'avatar/016.png', 2, '2011-07-07', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(17, 't.nakajima', 'hash017', '中島達也', 1, NULL, NULL, '2023-03-15', NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

1.1.2 デカルト積の発生と対策

単一テーブル検索では SELECT 列リスト FROM テーブル名; と記述します。複数テーブルからの検索は、テーブルリストをカンマ区切りで指定するだけです。

SELECT * FROM employee_data, division_master;

このクエリを実行すると、予想外の大量レコードが返却されます。上記の例では85件（employee_data 17件 × division_master 5件）の結果セットが生成されます。これは数学的なデカルト積（直積）が発生した現象です。

デカルト積とは、2つの集合（A集合とB集合）の全ての組み合わせを生成する演算です。データベースでは、テーブル同士の結合条件を明示しないと、この積集合が結果として返却されます。

業務アプリケーションでは、無関係な組み合わせレコードは無意味です。有効なデータのみを抽出するため、結合条件を付与する必要があります。

SELECT * FROM employee_data, division_master 
WHERE employee_data.division_code = division_master.division_code;

division_code が NULL の従業員（17番目のレコード）は、結合条件を満たさないため、結果セットから除外されます。

1.2 クエリの分類体系

複数テーブルクエリは大きく3つのカテゴリに分類されます：

結合クエリ（JOIN）
- 内部結合（INNER JOIN）：両テーブルの共通部分を抽出
- 左外部結合（LEFT OUTER JOIN）：左側テーブルの全レコードを含む
- 右外部結合（RIGHT OUTER JOIN）：右側テーブルの全レコードを含む
サブクエリ
- SELECT文の中に入れ子で記述するクエリ

内部結合は、従来のカンマ区切り構文 FROM テーブル1, テーブル2 WHERE ... と同等ですが、明確なJOINキーワードを使用する方が可読性が高く、現在の標準的な記法です。

外部結合は、LEFT JOIN（左テーブルを基準）とRIGHT JOIN（右テーブルを基準）の2種類が存在します。例えば、所属部署が未定の従業員も含めて全員を取得するにはLEFT JOINが適切です。

サブクエリは、WHERE句やSELECT句の中で別のSELECT文を実行する際に利用します。これにより、複雑な条件を段階的に記述できます。

タグ: SQL マルチテーブルクエリ JOIN デカルト積サブクエリ

5月15日 21:30 投稿

異端開発室