【MySQL】UNION や UNION ALL で複数のテーブルをまとめて検索する│株式会社シーポイントラボ

　データベース内ではしばしば共通項を持つが別のモノなので別のテーブルを定義する、ということをします。例えば、次のER図です。

　こういった構造のデータベースの上で両方のテーブルの会員をまとめて検索したい、といった要望が出てくる時があります。この要望を実現するよくある方法は WHERE、OFFSET、LIMIT あたりを組み合わせたひとつの SQL を発行する方法ですが、これをすんなり使うことはできません。各テーブルのクエリ結果についてどれが何件目で今なん件表示できるデータがそろっているのか、二つの結果をどうやって一つの表にまとめるか、で苦労します。解決方法の一つとして次のER図の様にデータベースの構造に検索用のテーブルを追加する、あるいは元のテーブルを変更する方法があります。

　こうすると次の様なクエリで共通項のみの検索ができます。共通項のみのテーブル上でインデックスを使えるので SQL の実行時間を短くするのも比較的容易です。

SELECT *
FROM member_names
WHERE name LIKE '佐藤%'
ORDER BY name
LIMIT 30 OFFSET 60; # 1ページ件数30件の3ページ目

　しかしながら、様々な理由でその様なデータベースの構造の変更ができない場合もあります。そういった際には UNION、UNION ALL といった句で複数の結果をまとめてその上で OFFSET、LIMIT を使う方法があります。これは次の様にできます。

(SELECT
     # UNION, UNION ALL は同じ列を持つ SELECT 結果をまとめます。
     # ある SELECT 結果特有の列をまとめた結果にも用いるならば、他の SELECT 結果で null 埋め列を作るのが手軽です。
     # ここでは商業会員になくゲスト会員にある列である guest_member_id を2列目として null 埋めをしています。
     commerce_member_id,
     null as guest_member_id,
     name
 FROM commerce_members
 WHERE name LIKE '%高%' # WHERE は個々のクエリに書く必要があります。
)
# @see https://dev.mysql.com/doc/refman/8.0/ja/union.html
# UNION ALL で同じ内容の行の重複を許してまとめます。
# 単に UNION とすると重複を除去します。
UNION ALL
(SELECT null,
        guest_member_id,
        name
 FROM guest_members
 WHERE name LIKE '%高%')
# 個々のクエリを括り、最後に ORDER BY, LIMIT, OFFSET を書くと
# UNIONした結果に ORDER BY, LIMIT, OFFSET が適用されます。
ORDER BY name
LIMIT 2 OFFSET 2 # 1ページ件数2件の2ページ目

デモ用SQL全体

create table commerce_members
(
    commerce_member_id int auto_increment,
    name               varchar(255) not null comment '名前',
    commerce_id        varchar(255) not null comment '商業ID',
    commerce_spacial_1 int default null comment '商業会員特有カラム1',
    commerce_spacial_2 int default null comment '商業会員特有カラム2',
    commerce_spacial_3 int default null comment '商業会員特有カラム3',
    commerce_spacial_4 int default null comment '商業会員特有カラム4',
    commerce_spacial_5 int default null comment '商業会員特有カラム5',
    created_at         datetime     null,
    updated_at         datetime     null,
    constraint commerce_members_pk
        primary key (commerce_member_id)
);

alter table commerce_members comment '商業会員';

INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (1, '今野 角太郎', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (2, '上田 倉美', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (3, '古田 新太郎', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (4, '吉本 兼典', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (5, '日高 義隆', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (6, '本間 僧三郎', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (7, '根岸 三平', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (8, '藤沢 なつみ', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (9, '野村 敬吉', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (10, '平山 朋子', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (11, '上原 道正', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (12, '石山 美世', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (13, '植田 伴助', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (14, '松山 麻琴', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (15, '大橋 知美', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (16, '村山 裕史', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (17, '岡部 光善', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (18, '堀内 昭子', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (19, '宮内 正進', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (20, '三木 禎二', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (21, '高山 信弥', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (22, '竹内 政年', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (23, '村瀬 理香', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (24, '細川 真人', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (25, '高井 清一', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (26, '菅野 今朝雄', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');
INSERT INTO commerce_members (commerce_member_id, name, commerce_id, commerce_spacial_1, commerce_spacial_2, commerce_spacial_3, commerce_spacial_4, commerce_spacial_5, created_at, updated_at) VALUES (27, '相馬 茂一', 'exampleID', null, null, null, null, null, '2022-05-09 11:02:34', '2022-05-09 11:02:36');

create table guest_members
(
    guest_member_id int auto_increment,
    name               varchar(255) not null comment '名前',
    guest_spacial_1 int default null comment 'ゲスト会員特有カラム1',
    guest_spacial_2 int default null comment 'ゲスト会員特有カラム2',
    guest_spacial_3 int default null comment 'ゲスト会員特有カラム3',
    guest_spacial_4 int default null comment 'ゲスト会員特有カラム4',
    guest_spacial_5 int default null comment 'ゲスト会員特有カラム5',
    created_at         datetime     null,
    updated_at         datetime     null,
    constraint guest_members_pk
        primary key (guest_member_id)
);

alter table guest_members comment 'ゲスト会員';

INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (1, '宮沢 玲子', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (2, '榎本 準郎', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (3, '星野 成男', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (4, '萩原 利次', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (5, '本田 利浩', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (6, '塚田 佳久', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (7, '花田 好行', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (8, '森 幸博', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (9, '沢田 知明', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (10, '柴田 喜重', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (11, '中尾 慶太郎', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (12, '渋谷 勝行', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (13, '渋谷 幾江', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (14, '谷 ハルノ', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (15, '吉井 絵里', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (16, '松尾 恒宏', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');
INSERT INTO guest_members (guest_member_id, name, guest_spacial_1, guest_spacial_2, guest_spacial_3, guest_spacial_4, guest_spacial_5, created_at, updated_at) VALUES (17, '岡崎 三男', null, null, null, null, null, '2022-05-09 11:06:31', '2022-05-09 11:06:31');

(SELECT
     # UNION, UNION ALL は同じ列を持つ SELECT 結果をまとめます。
     # ある SELECT 結果特有の列をまとめた結果にも用いるならば、他の SELECT 結果で null 埋め列を作るのが手軽です。
     # ここでは商業会員になくゲスト会員にある列である guest_member_id を2列目として null 埋めをしています。
     commerce_member_id,
     null as guest_member_id,
     name
 FROM commerce_members
 WHERE name LIKE '%高%' # WHERE は個々のクエリに書く必要があります。
)
# @see https://dev.mysql.com/doc/refman/8.0/ja/union.html
# UNION ALL で同じ内容の行の重複を許してまとめます。
# 単に UNION とすると重複を除去します。
UNION ALL
(SELECT null,
        guest_member_id,
        name
 FROM guest_members
 WHERE name LIKE '%高%')
# 個々のクエリを括り、最後に ORDER BY, LIMIT, OFFSET を書くと
# UNIONした結果に ORDER BY, LIMIT, OFFSET が適用されます。
ORDER BY name
LIMIT 2 OFFSET 2 # 1ページ件数2件の2ページ目

　これにより一つのクエリで複数のテーブルをまとめて検索できます。
　
　ちなみに PHP フレームワークの Laravel では次の様に union メソッドとクエリオブジェクトを組み合わせることで UNION 句を使えます。
データベース：クエリビルダ 9.x Laravel

use Illuminate\Support\Facades\DB;

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

【MySQL】UNION や UNION ALL で複数のテーブルをまとめて検索する

【Docker】【MySQL】MySQL イメージのコンテナを初期起動した際に自動で *.sql ファイル内の SQL 文を実行する

【MySQL】あるクエリ結果をあるテーブル中のレコードとして永続化するクエリ

株式会社シーポイントラボ