MySQLで別の列に指定した共通値を持つタプルを抽出する方法

MySQLで、ある列に指定した値と別の列に指定した値を両方持つタプル（行の組み合わせ）を抽出したい場合、自己結合（セルフジョイン）を活用するのが効果的です。同じテーブルに別名（エイリアス）を付けてINNER JOINし、WHERE句でそれぞれの条件を指定することで、目的のタプルを取得できます。

基本構文

指定した共通値を持つタプルを抽出するには、以下の構文を使用します。

SELECT DISTINCT
  aliasName.yourColumnName1,
  aliasName.yourColumnName2,
  aliasName1.yourColumnName1,
  aliasName1.yourColumnName2
FROM yourTableName aliasName
INNER JOIN yourTableName aliasName1
  ON aliasName.yourColumnName1 = aliasName1.yourColumnName1
WHERE aliasName.yourColumnName2 = 'value1'
  AND aliasName1.yourColumnName2 = 'value2';

ポイントは、同一テーブルに2つの別名を付けて結合し、それぞれの別名に対して異なる条件をWHERE句で指定する点です。これにより、「列Aが同じ値を持ち、かつ列Bの値が異なる2つの条件に一致する行」を組み合わせて抽出できます。

サンプルテーブルの作成

実際に動作を確認するため、まずテーブルを作成しましょう。

mysql> create table extractTuples
    -> (
    -> Id int,
    -> Name varchar(20),
    -> Comments text
    -> );
Query OK, 0 rows affected (0.77 sec)

テストデータの挿入

次に、INSERT文を使ってサンプルレコードを登録します。

mysql> insert into extractTuples values(1,'John','hi');
Query OK, 1 row affected (0.20 sec)
mysql> insert into extractTuples values(2,'Carol','hello');
Query OK, 1 row affected (0.17 sec)
mysql> insert into extractTuples values(3,'John','Amazing');
Query OK, 1 row affected (0.13 sec)
mysql> insert into extractTuples values(1,'Carol','Good');
Query OK, 1 row affected (0.14 sec)

登録データの確認

SELECT文ですべてのレコードを表示して、テーブルの中身を確認します。

mysql> select *from extractTuples;

出力結果

+------+-------+----------+
| Id   | Name  | Comments |
+------+-------+----------+
|    1 | John  | hi       |
|    2 | Carol | hello    |
|    3 | John  | Amazing  |
|    1 | Carol | Good     |
+------+-------+----------+
4 rows in set (0.00 sec)

このテーブルには、Id=1の行が2つあり、それぞれ「John」と「Carol」という異なるNameが紐づいています。

共通値を持つタプルを抽出するクエリ

それでは、Idが共通し、Nameがそれぞれ「John」と「Carol」であるタプルを抽出してみましょう。

mysql> SELECT DISTINCT tbl.Id,tbl.Name,tbl1.Id,tbl1.Name
    -> FROM extractTuples tbl
    -> INNER JOIN extractTuples tbl1
    -> ON tbl.Id = tbl1.Id
    -> WHERE tbl.Name = 'John' AND tbl1.Name = 'Carol';

出力結果

+------+------+------+-------+
| Id   | Name | Id   | Name  |
+------+------+------+-------+
|    1 | John |    1 | Carol |
+------+------+------+-------+
1 row in set (0.00 sec)

解説

このクエリでは、テーブル「extractTuples」に「tbl」と「tbl1」という2つの別名を付けて自己結合しています。結合条件は「tbl.Id = tbl1.Id」、つまりIdが一致する行同士を組み合わせます。さらにWHERE句で「tbl.Name = 'John'」と「tbl1.Name = 'Carol'」という2つの条件を課すことで、同じIdを共有しながら、片方はJohn、もう片方はCarolである行のペアだけが結果として返されます。

SELECT DISTINCTを使用しているのは、結合によって重複行が発生する可能性があるためです。DISTINCTを付けることで、重複のないタプルをきれいに取得できます。

この手法は、同一テーブル内で関連する複数のレコードをペアとして抽出したい場合（例：同じ注文IDに対する異なるユーザーの操作履歴など）に広く応用できます。