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

MySQLの自己結合を解説します。

自己結合

結合は２つのテーブルを繋げて、１つの新しい表データを作成することです。

自己結合は内部結合や外部結合などにおいて、結合する２つのテーブルに同じテーブルを使います。

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

/* 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カラムとparent_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 |
+----+-------------+-----------+----+-------------+-----------+

利用方法

自己結合では２つのテーブルに同じテーブルを使います。
そのため、ASで別名を付けて明示的に記述します。

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)

mysql> SELECT * FROM categories AS ctg1
    -> INNER JOIN categories AS ctg2 ON ctg1.id = ctg2.parent_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 |
+----+-------------+-----------+----+-------------+-----------+
6 rows in set (0.00 sec)

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

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;
+----+-------------+-----------+----+-------------+-----------+----+------------+-----------+
| 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)