SQLでのユニオンの使い方

9か月 ago

6 minutes

著者は「Write for Donationsプログラム」の一環として、寄付をするために、Diversity in Tech Fundを選びました。

はじめに

多くのデータベースは、意味とコンテキストに基づいて情報を異なるテーブルに分散させています。データベース内に保持されているデータに関する情報を取得する際には、通常、複数のテーブルを同時に参照したいと思うことがあります。

Structured Query Language (SQL)は、異なるテーブルからデータを取得するための複数の方法を提供しています。具体的には、集合演算などが利用できます。特に、集合演算子のUNIONは、ほとんどのリレーショナルデータベースシステムで広くサポートされています。UNION演算は、一致する列を持つ2つのクエリの結果を取り、1つにマージします。

このガイドでは、UNION操作を使用して複数のテーブルから同時にデータを取得し、結果を結合します。さらに、UNION演算子をフィルタリングと組み合わせて結果を並べ替えます。

前提条件

このガイドに従うためには、SQLベースの関係データベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境を使用して検証されました。

A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
Basic familiarity with executing SELECT queries to select data from the database, as described in our How To SELECT Rows FROM Tables in SQL guide.

Note

注意: このチュートリアルで説明されているコマンドは、ほとんどのRDBMSで動作するとともに、標準のSQL構文の一部ですが、多くのRDBMSは独自のSQL実装を使用していることに注意してください。そのため、MySQL以外のシステムでテストする場合、正確な構文や出力は異なる可能性があります。

ネイティブな日本語で以下を言い換える：
UNION操作の使い方を練習するために、いくつかのサンプルデータでテーブルがロードされたデータベースが必要です。このガイド全体で使用されるサンプルデータベースを作成するためのMySQLサーバーへの接続方法の詳細については、以下のセクション「MySQLへの接続とサンプルデータベースのセットアップ」をご覧ください。

MySQLに接続し、サンプルのデータベースをセットアップする。

このセクションでは、MySQLサーバーに接続し、サンプルデータベースを作成することで、このガイドの例に従うことができます。

リモートサーバでSQLデータベースシステムを実行している場合、ローカルマシンからサーバにSSH接続してください。

ssh sammy@your_server_ip

サミーの部分を、自分のMySQLユーザーアカウントの名前で置き換え、MySQLサーバープロンプトを開いてください。

mysql -u sammy -p

書店というデータベースを作成してください。 (Shoten to iu databēsu wo sakusei shite kudasai)

CREATE DATABASE bookstore;

データベースが正常に作成された場合、以下のような出力結果が表示されます。

Output

Query OK, 1 row affected (0.01 sec)

以下のUSEステートメントを実行して、書店データベースを選択してください。

USE bookstore;

以下の出力結果を受け取ります。 (Anata wa shita no shutsuryoku kekka o uketorimasu.)

Output

Database changed

データベースを選択したら、その中にサンプルのテーブルを作成することができます。このガイドの目的のために、書籍の購入と貸出の両方を提供する架空の書店を使用します。両方のサービスは個別に管理されているため、購入と貸出に関するデータは別々のテーブルに保存されます。

Note

注意：この例のデータベースのスキーマは教育目的のために単純化されています。実際のシナリオでは、テーブルの構造はより複雑で、主キーと外部キーが関与しています。データベースがデータをどのように整理するかについての詳細は、弊社のリレーショナルデータベースの理解に関するチュートリアルを参照してください。

最初のテーブルであるbook_purchasesは、購入された本と購入を行った顧客に関するデータを保持します。このテーブルには4つの列があります。

purchase_id: This column holds the purchase identifier, represented by the int data type. This column will become the table’s primary key, with each value becoming a unique identifier for its respective row.
customer_name: This column will hold customer’s name, expressed using the varchar data type with a maximum of 30 characters.
book_title: This column will hold the purchased book’s title, expressed using the varchar data type with a maximum of 200 characters.
date: Using the date data type, this column will hold the date of each purchase.

以下のコマンドを使用して、サンプルテーブルを作成してください。

CREATE TABLE book_purchases (
purchase_id int,
customer_name varchar(30),
book_title varchar(40),
date date,
PRIMARY KEY (purchase_id)
);

もし以下の出力が表示されたならば、最初のテーブルが作成されました。

Output

Query OK, 0 rows affected (0.00 sec)

第2のテーブルはbook_leasesと呼ばれ、借りられた本に関する情報を保存します。前のテーブルと同様の構造ですが、貸出は2つの異なる日付で特徴付けられます：貸出日と貸出期間。これを表現するために、leasesテーブルには5つの列が含まれます。

lease_id: This column holds the lease identifier, represented by the int data type. This column will become the table’s primary key, with each value becoming a unique identifier for its respective row.
customer_name: This column will hold customer’s name, expressed using the varchar data type with a maximum of 30 characters.
book_title: This column will hold borrowed book’s title, expressed using the varchar data type with a maximum of 200 characters.
date_from: Using the date data type, this column will hold the start date of a lease.
date_to: Using the date data type, this column will hold the end date of a lease.

以下のコマンドで2番目のテーブルを作成してください。

CREATE TABLE book_leases (
lease_id int,
customer_name varchar(30),
book_title varchar(40),
date_from date,
date_to date,
PRIMARY KEY (lease_id)
);

以下の出力は、2番目のテーブルの作成を確認しています。

Output

Query OK, 0 rows affected (0.00 sec)

その後、次のINSERT INTO操作を実行して、購入テーブルにいくつかのサンプルデータをロードします。

INSERT INTO book_purchases
VALUES
(1, ‘sammy’, ‘The Picture of Dorian Gray’, ‘2022-10-01’),
(2, ‘sammy’, ‘Pride and Prejudice’, ‘2022-10-04’),
(3, ‘sammy’, ‘The Time Machine’, ‘2022-09-23’),
(4, ‘bill’, ‘Frankenstein’, ‘2022-07-23’),
(5, ‘bill’, ‘The Adventures of Huckleberry Finn’, ‘2022-10-01’),
(6, ‘walt’, ‘The Picture of Dorian Gray’, ‘2022-04-15’),
(7, ‘walt’, ‘Frankenstein’, ‘2022-10-13’),
(8, ‘walt’, ‘Pride and Prejudice’, ‘2022-10-19’);

INSERT INTO操作は、指定された値でbook_purchasesテーブルに8つの購入データを追加します。以下の出力は、8つの行がすべて追加されたことを示しています。

Output

Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0

その後、book_leasesテーブルにサンプルデータを挿入してください。

INSERT INTO book_leases
VALUES
(1, ‘sammy’, ‘Frankenstein’, ‘2022-09-14’, ‘2022-11-14’),
(2, ‘sammy’, ‘Pride and Prejudice’, ‘2022-10-01’, ‘2022-12-31’),
(3, ‘sammy’, ‘The Adventures of Huckleberry Finn’, ‘2022-10-01’, ‘2022-12-01’),
(4, ‘bill’, ‘The Picture of Dorian Gray’, ‘2022-09-03’, ‘2022-09-18’),
(5, ‘bill’, ‘Crime and Punishment’, ‘2022-09-27’, ‘2022-12-05’),
(6, ‘kim’, ‘The Picture of Dorian Gray’, ‘2022-10-01’, ‘2022-11-15’),
(7, ‘kim’, ‘Pride and Prejudice’, ‘2022-09-08’, ‘2022-11-17’),
(8, ‘kim’, ‘The Time Machine’, ‘2022-09-04’, ‘2022-10-23’);

以下の出力が表示されます。これによって、サンプルデータが追加されたことが確認されます。

Output

Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0

リースと購入は、類似した顧客と書籍タイトルに関連しており、UNION演算子の動作を示すために役立つでしょう。

それでは、あとはガイドの残りを追って、SQLでUNION操作を使い始める準備が整いました。

UNION演算子の構文の理解

SQLのUNION演算子は、データベースに対して個別のSELECTクエリを通じて取得した2つの別々の結果セットをマージし、両方のクエリから返された行を含む1つの結果セットにするよう指示します。

Note

注意：データベースでは、UNIONとともに使用されるSELECTクエリの複雑さを制限しません。データの取得クエリには、JOIN文、集計、またはサブクエリを含めることができます。しばしば、UNIONは複雑な文から得られた結果を統合するために使用されます。教育目的のため、このガイドでは、UNION演算子の振る舞いに焦点を当てるため、SELECTクエリの例を使用します。

次の例では、UNION演算子を含むSQL文の一般的な構文が示されています。

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

このSQLフラグメントは、最初にテーブル1から2つの列を返すSELECT文で始まり、そしてUNION演算子と2番目のSELECT文が続きます。2番目のSELECTクエリも2つの列を返しますが、それはテーブル2からです。UNIONキーワードは、データベースに前後のクエリを別々に実行し、その結果セットを一つに結合するように指示します。両方のSELECTクエリとそれらの間のUNIONキーワードを含むこのコードフラグメントは、単一のSQL文です。そのため、最初のSELECTクエリはセミコロンで終わらず、全体の文の後にのみ表示されます。

例えば、本を購入したか貸し出した顧客のリストを作成したいとします。購入の記録は「book_purchases」テーブルに保持されており、一方、貸し出しは「book_leases」テーブルに保存されています。以下のクエリを実行してください。

SELECT customer_name FROM book_purchases
UNION
SELECT customer_name FROM book_leases;

こちらがこのクエリの結果セットです。

Output

この出力は、サミー、ビル、ワルト、キムのいずれかが何らかのタイミングで本を購入またはリースしたことを示しています。この結果セットがどのように生成されたかを理解するには、購入について1度、リースについて1度、それぞれSELECT文を実行してみてください。

本を購入した顧客を返すために、以下のクエリを実行してください。

SELECT customer_name FROM book_purchases;

以下の出力は画面に表示されます。

Output

サミー、ビル、そしてウォルトは本を購入しましたが、キムはまだ購入していません。

次に、本を借りた顧客を返すためにクエリを実行してください。 (Tsugi ni, honwo karita kokyaku wo kaesu tame ni kueri wo jikkou shite kudasai)

SELECT customer_name FROM book_leases;

次の出力は画面に表示されます：

Output

+—————+ | customer_name | +—————+ | sammy | | sammy | | sammy | | bill | | bill | | kim | | kim | | kim | +—————+ 8 rows in set (0.000 sec)

リーステーブルはサミー、ビル、キムに関連していますが、ワルトは決して本を借りません。これらの2つの回答を組み合わせることで、リースと購入の両方のデータを取得できます。

UNIONと2つのクエリを別々に実行することの重要な違いは、結果をマージするだけでなく、重複する値を削除することです。その結果には、顧客の名前が繰り返し表示されることはありません。

2つの別々のクエリの結果を正しくマージするために、UNIONを使用するためには両方のクエリが同じ形式で結果を返す必要があります。いくつかの相違点はデータベースエンジンのエラーを引き起こし、他の相違点はクエリの意図に一致しない結果を返します。

以下に示す2つの例を考えてみてください：

カラム数が一致しない場合のUNION

単一の列を返すSELECT文と、2つの列を返す別のSELECT文の間にUNIONを実行してみてください。

SELECT purchase_id, customer_name FROM book_purchases
UNION
SELECT customer_name FROM book_leases;

データベースサーバーはエラーで返信します。 (De-ta-be-su sa-ba- wa e-ra- de hen-shin shi-masu.)

Output

The used SELECT statements have a different number of columns

異なる列数を持つ結果セットに対して、UNION操作はできません。

列の順序が異なるUNION

同じ値を返すが、順序が異なる2つのSELECTステートメントの間でUNIONを実行してみてください。

SELECT customer_name, book_title FROM book_purchases
UNION
SELECT book_title, customer_name FROM book_leases;

データベースサーバーはエラーを返さないが、結果セットは正しくない場合があります。

Output

この例では、UNION演算子は最初のクエリの最初の列と2番目のクエリの最初の列を結合し、同様に2番目の列も結合します。顧客名と書籍のタイトルを混ぜ合わせます。

WHERE句とUNIONを組み合わせて利用して、順序付けを行う方法を使用する。

前の例では、2つの対応するテーブルのすべての行を表す結果セットをマージしました。しかし、しばしば結果をマージする前に行をフィルタリングする必要があります。UNION演算子でマージされるSELECT文は、WHERE句を使用して行をフィルタリングすることができます。

あなたが書店の協力を得て、サミーがどの本を購入または貸し出しで読んでいるか知りたいと仮定します。次のクエリを実行してください。

SELECT book_title FROM book_purchases
WHERE customer_name = ‘Sammy’
UNION
SELECT book_title FROM book_leases
WHERE customer_name = ‘Sammy’;

両方のSELECTクエリには、Sammyによる購入とリースを含む行をフィルタリングするWHERE句が含まれます。このクエリの結果セットは次のように表示されます。

Output

もう一度言いますが、UNIONは結果のリストに重複がないようにします。SELECTクエリのどちらか、または両方で返される行を制限するために、WHERE句を使用することができます。さらに、WHERE句は両文で異なる列や条件を参照することができます。

UNION演算子を通じて返される結果には特定の順序がありません。それを変更するには、ORDER BY句を利用することができます。順序付けは、個々のクエリではなく最終的なマージされた結果に対して行われます。

サミーが購入またはリースしたすべての本のリストを取得した後、書籍タイトルをアルファベット順にソートするために、以下のクエリを実行してください。

SELECT book_title FROM book_purchases
WHERE customer_name = ‘Sammy’
UNION
SELECT book_title FROM book_leases
WHERE customer_name = ‘Sammy’
ORDER BY book_title;

以下の出力は、画面に表示されます。

Output

今回は、book_title列に基づいて両方のSELECTクエリの結果がマージされた順序で結果が返されます。

重複を保持するためにUNION ALLを使用する。

前の例が示しているように、UNION演算子は結果から重複行を自動的に除去します。ただし、クエリで意図した結果や期待した動作とは異なる場合もあります。たとえば、2022年10月1日に購入または賃借された書籍に興味があるとします。これらのタイトルを取得するには、以前と同じような例に従うことができます。

SELECT book_title FROM book_purchases
WHERE date = ‘2022-10-01’
UNION
SELECT book_title FROM book_leases
WHERE date_from = ‘2022-10-01’
ORDER BY book_title;

以下の結果が得られます。

Output

返却された本のタイトルは正しいですが、結果からはこれらの本が購入されたものか、貸与されたものか、あるいは両方かを判断することができません。一部の本が購入と貸与の両方であった場合、そのタイトルはbook_purchasesテーブルとbook_leasesテーブルの両方に表示されます。ただし、UNIONによる重複行の取り除きにより、その情報は結果から失われてしまいます。

幸いにも、SQLにはこの動作を変更し、重複した行を保持する方法があります。 UNION ALL演算子を使用して、重複した行を削除せずに2つのクエリの結果をマージすることができます。 UNION ALLはUNIONと同様に機能しますが、同じ値の複数の出現がある場合は、すべてが結果に表示されます。

同じクエリを実行し、UNIONをUNION ALLに変更して実行してください。

SELECT book_title FROM book_purchases
WHERE date = ‘2022-10-01’
UNION ALL
SELECT book_title FROM book_leases
WHERE date_from = ‘2022-10-01’
ORDER BY book_title;

今回は、結果のリストが長くなります。

Output

ふたつの本、『ハックルベリー・フィンの冒険』と『ドリアン・グレイの肖像』が検索結果の中で2回表示されます。これは、これらのタイトルがbook_purchasesテーブルとbook_leasesテーブルの両方に登場していることを意味します。重複したエントリーの場合は、その日に両方の貸出と購入が行われたと想定できます。

重複を削除したい場合と保持したい場合に応じて、交換可能に使用できるUNIONおよびUNION ALLオペレーターの間から選択することができます。

Note

注意：UNION ALLを実行する方がUNIONを実行するよりも速くなります。データベースは重複をスキャンする必要がないためです。重複行を含まないとわかっている2つのSELECTクエリの結果をマージする場合、UNION ALLを使用すると大きなデータセットでも noticeable なパフォーマンスの向上が得られる場合があります。

結論

このガイドに従うことで、UNIONおよびUNION ALL操作を使用して複数のテーブルからデータを取得しました。また、結果をフィルタリングするためにWHERE句を使用し、並べ替えるためにORDER BY句を使用しました。最後に、SELECT文が異なるデータ形式を返す場合の可能なエラーや予期しない振る舞いについて学びました。

ここに含まれているコマンドはほとんどのリレーショナルデータベースで動作するはずですが、SQLデータベースはそれぞれ固有の言語実装を使用していることに注意してください。（これらの違いについては、当社のガイド「SQLite vs MySQL vs PostgreSQL：リレーショナルデータベース管理システムの比較」を参照してください）。各コマンドとその完全なオプションセットのより詳細な説明については、RDBMSの公式ドキュメントを参照してください。

もしSQL言語に関する概念やそれとの作業についてさらに学びたい場合は、『SQLの使い方シリーズ』の他のガイドもぜひチェックしてみてください。