【MySQL】自己結合 - テーブル結合

MySQLの自己結合について解説します。

検証環境

自己結合

自己結合は“同じテーブルのレコード同士を繋げる結合”です。

次のcategoriesテーブルで具体例を示します。

/* categoriesテーブル */
+----+-------------+-----------+
| id | name        | parent_id |
+----+-------------+-----------+
|  1 | DEVELOPMENT |      NULL |
|  2 | PROGRAMMING |         1 |
|  3 | DATABASE    |         1 |
|  4 | PHP         |         2 |
|  5 | JavaScript  |         2 |
|  6 | MySQL       |         3 |
|  7 | PostgreSQL  |         3 |
+----+-------------+-----------+

categoriesテーブルはparent_idカラムに自身のテーブルのidカラムのデータを格納します。

この２のカラムで自己結合した結果は次の表データです。

+----+-------------+-----------+----+-------------+-----------+
| id | name        | parent_id | id | name        | parent_id |
+----+-------------+-----------+----+-------------+-----------+
|  1 | DEVELOPMENT |      NULL |  2 | PROGRAMMING |         1 |
|  1 | DEVELOPMENT |      NULL |  3 | DATABASE    |         1 |
|  2 | PROGRAMMING |         1 |  4 | PHP         |         2 |
|  2 | PROGRAMMING |         1 |  5 | JavaScript  |         2 |
|  3 | DATABASE    |         1 |  6 | MySQL       |         3 |
|  3 | DATABASE    |         1 |  7 | PostgreSQL  |         3 |
+----+-------------+-----------+----+-------------+-----------+

同じテーブルの各レコードが２カラムを基点に結合されています。

基本構文

自己結合は内部結合や外部結合で同じテーブルを使います。

SELECT 表示カラム FROM テーブルX AS 別名A [ INNER | LEFT | RIGHT ] JOIN テーブルX AS 別名B ON 別名A.カラム = 別名B.カラム

通常の結合と異なる点は、クエリ内に同じテーブル名を使うとエラーが発生するため、一方（または両方）にAS句で別名を付ける必要があることです。

サンプル

Command Line

mysql> SELECT * FROM categories;
+----+-------------+-----------+
| id | name        | parent_id |
+----+-------------+-----------+
|  1 | DEVELOPMENT |      NULL |
|  2 | PROGRAMMING |         1 |
|  3 | DATABASE    |         1 |
|  4 | PHP         |         2 |
|  5 | JavaScript  |         2 |
|  6 | MySQL       |         3 |
|  7 | PostgreSQL  |         3 |
+----+-------------+-----------+
7 rows in set (0.00 sec)

___ih_hl_start
mysql> SELECT * FROM categories AS ctg1
    -> INNER JOIN categories AS ctg2 ON ctg1.id = ctg2.parent_id;
___ih_hl_end
+----+-------------+-----------+----+-------------+-----------+
| id | name        | parent_id | id | name        | parent_id |
+----+-------------+-----------+----+-------------+-----------+
|  1 | DEVELOPMENT |      NULL |  2 | PROGRAMMING |         1 |
|  1 | DEVELOPMENT |      NULL |  3 | DATABASE    |         1 |
|  2 | PROGRAMMING |         1 |  4 | PHP         |         2 |
|  2 | PROGRAMMING |         1 |  5 | JavaScript  |         2 |
|  3 | DATABASE    |         1 |  6 | MySQL       |         3 |
|  3 | DATABASE    |         1 |  7 | PostgreSQL  |         3 |
+----+-------------+-----------+----+-------------+-----------+
6 rows in set (0.00 sec)

１５〜１６行目が自己結合のクエリです。

内部結合でcategoriesテーブルにcategoriesテーブルを結合しています。

また、他の結合と同様に任意の数のテーブルを結合することが可能です。

Command Line

___ih_hl_start
mysql> SELECT * FROM categories AS ctg1
    -> INNER JOIN categories AS ctg2 ON ctg1.id = ctg2.parent_id
    -> INNER JOIN categories AS ctg3 ON ctg2.id = ctg3.parent_id;
___ih_hl_end
+----+-------------+-----------+----+-------------+-----------+----+------------+-----------+
| id | name        | parent_id | id | name        | parent_id | id | name       | parent_id |
+----+-------------+-----------+----+-------------+-----------+----+------------+-----------+
|  1 | DEVELOPMENT |      NULL |  2 | PROGRAMMING |         1 |  4 | PHP        |         2 |
|  1 | DEVELOPMENT |      NULL |  2 | PROGRAMMING |         1 |  5 | JavaScript |         2 |
|  1 | DEVELOPMENT |      NULL |  3 | DATABASE    |         1 |  6 | MySQL      |         3 |
|  1 | DEVELOPMENT |      NULL |  3 | DATABASE    |         1 |  7 | PostgreSQL |         3 |
+----+-------------+-----------+----+-------------+-----------+----+------------+-----------+
4 rows in set (0.00 sec)