［MariaDB］テーブル結合（CROSS JOIN、INNER JOIN、LEFT JOIN）

データベースとテーブルの作成

CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 記事テーブル
CREATE TABLE articles (
  id       BIGINT PRIMARY KEY AUTO_INCREMENT,
  title    VARCHAR(255) NOT NULL,
  content  TEXT NOT NULL
);

# タグテーブル
CREATE TABLE tags (
  id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(100) NOT NULL
);

# 中間テーブル（記事とタグの関連付け）
CREATE TABLE article_tag (
  article_id  BIGINT NOT NULL,
  tag_id      BIGINT NOT NULL,   
  PRIMARY KEY (article_id, tag_id)
);


# 記事
insert into articles (id, title, content) values 
(1, 'Linux1', '内容1'),
(2, 'Linux2', '内容2'),
(3, 'Linux3', '内容3'),
(4, 'PHP1', '内容4'),
(5, 'PHP2', '内容5');

# タグ
insert into tags (id, name) values 
(1, 'IT'), 
(2, 'Linux'), 
(3, 'PHP');

# 中間テーブル（記事とタグの関連付け）
insert into article_tag (article_id, tag_id)  values 
(1, 1), # Linux1 - IT
(1, 2), # Linux1 - Linux
(2, 1), # Linux2 - IT
(2, 2), # Linux2 - Linux
(3, 1), # Linux3 - IT
(3, 2), # Linux3 - Linux
(4, 1), # PHP1 - IT
(4, 3), # PHP1 - PHP
(5, 1), # PHP2 - IT
(5, 3); # PHP2 - PHP

テーブル1の全てのレコードの集合と、テーブル2の全てのレコードの集合の、全ての組み合わせで結合することをクロス結合と呼び、結果のことをデカルト積と呼びます。
単純にレコード同士を組み合わせるため、整合性が取れないデータも含まれます。
ちなみに、クロス結合に対して検索条件を指定して、結果がデカルト積にならない場合は、クロス結合とは呼びません。実行自体は可能です。

SELECT a.*, at.* FROM articles a CROSS JOIN article_tag at;

または

SELECT a.*, at.* FROM articles a, article_tag at;

+----+--------+---------+------------+--------+
| id | title  | content | article_id | tag_id |
+----+--------+---------+------------+--------+
|  1 | Linux1 | 内容1   |          1 |      1 |
|  2 | Linux2 | 内容2   |          1 |      1 |
|  3 | Linux3 | 内容3   |          1 |      1 |
|  4 | PHP1   | 内容4   |          1 |      1 |
|  5 | PHP2   | 内容5   |          1 |      1 |
|  1 | Linux1 | 内容1   |          1 |      2 |
|  2 | Linux2 | 内容2   |          1 |      2 |
|  3 | Linux3 | 内容3   |          1 |      2 |
|  4 | PHP1   | 内容4   |          1 |      2 |
|  5 | PHP2   | 内容5   |          1 |      2 |
|  1 | Linux1 | 内容1   |          2 |      1 |
|  2 | Linux2 | 内容2   |          2 |      1 |
|  3 | Linux3 | 内容3   |          2 |      1 |
|  4 | PHP1   | 内容4   |          2 |      1 |
|  5 | PHP2   | 内容5   |          2 |      1 |
|  1 | Linux1 | 内容1   |          2 |      2 |
|  2 | Linux2 | 内容2   |          2 |      2 |
|  3 | Linux3 | 内容3   |          2 |      2 |
|  4 | PHP1   | 内容4   |          2 |      2 |
|  5 | PHP2   | 内容5   |          2 |      2 |
|  1 | Linux1 | 内容1   |          3 |      1 |
|  2 | Linux2 | 内容2   |          3 |      1 |
|  3 | Linux3 | 内容3   |          3 |      1 |
|  4 | PHP1   | 内容4   |          3 |      1 |
|  5 | PHP2   | 内容5   |          3 |      1 |
|  1 | Linux1 | 内容1   |          3 |      2 |
|  2 | Linux2 | 内容2   |          3 |      2 |
|  3 | Linux3 | 内容3   |          3 |      2 |
|  4 | PHP1   | 内容4   |          3 |      2 |
|  5 | PHP2   | 内容5   |          3 |      2 |
|  1 | Linux1 | 内容1   |          4 |      1 |
|  2 | Linux2 | 内容2   |          4 |      1 |
|  3 | Linux3 | 内容3   |          4 |      1 |
|  4 | PHP1   | 内容4   |          4 |      1 |
|  5 | PHP2   | 内容5   |          4 |      1 |
|  1 | Linux1 | 内容1   |          4 |      3 |
|  2 | Linux2 | 内容2   |          4 |      3 |
|  3 | Linux3 | 内容3   |          4 |      3 |
|  4 | PHP1   | 内容4   |          4 |      3 |
|  5 | PHP2   | 内容5   |          4 |      3 |
|  1 | Linux1 | 内容1   |          5 |      1 |
|  2 | Linux2 | 内容2   |          5 |      1 |
|  3 | Linux3 | 内容3   |          5 |      1 |
|  4 | PHP1   | 内容4   |          5 |      1 |
|  5 | PHP2   | 内容5   |          5 |      1 |
|  1 | Linux1 | 内容1   |          5 |      3 |
|  2 | Linux2 | 内容2   |          5 |      3 |
|  3 | Linux3 | 内容3   |          5 |      3 |
|  4 | PHP1   | 内容4   |          5 |      3 |
|  5 | PHP2   | 内容5   |          5 |      3 |
+----+--------+---------+------------+--------+

単純な組み合わせのため、articles の id と article_tag が article_id が異なる行が存在します。

イメージ的には、クロス結合の結果に対して、ON の後ろの結合条件でフィルタリングしたものが INNER JOIN です。
実際にはクロス結合は行われず、最適化して処理されます。

articles と article_tag を、id と article_id で結合する。
→articles と article_tag をクロス結合した結果から、id と article_id が同じ行を取得する。

SELECT a.*, at.* 
FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id;

+----+--------+---------+------------+--------+
| id | title  | content | article_id | tag_id |
+----+--------+---------+------------+--------+
|  1 | Linux1 | 内容1   |          1 |      1 |
|  1 | Linux1 | 内容1   |          1 |      2 |
|  2 | Linux2 | 内容2   |          2 |      1 |
|  2 | Linux2 | 内容2   |          2 |      2 |
|  3 | Linux3 | 内容3   |          3 |      1 |
|  3 | Linux3 | 内容3   |          3 |      2 |
|  4 | PHP1   | 内容4   |          4 |      1 |
|  4 | PHP1   | 内容4   |          4 |      3 |
|  5 | PHP2   | 内容5   |          5 |      1 |
|  5 | PHP2   | 内容5   |          5 |      3 |
+----+--------+---------+------------+--------+

ON の後ろの結合条件の「キー = キー」は、構文ではなく、あくまでもフィルタリングの条件のため、次のような条件を書くこともできます。

SELECT a.*, at.* 
FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id AND a.id >= 3;

+----+--------+---------+------------+--------+
| id | title  | content | article_id | tag_id |
+----+--------+---------+------------+--------+
|  3 | Linux3 | 内容3   |          3 |      1 |
|  3 | Linux3 | 内容3   |          3 |      2 |
|  4 | PHP1   | 内容4   |          4 |      1 |
|  4 | PHP1   | 内容4   |          4 |      3 |
|  5 | PHP2   | 内容5   |          5 |      1 |
|  5 | PHP2   | 内容5   |          5 |      3 |
+----+--------+---------+------------+--------+

一応上記のように書くこともできますが、通常は、テーブル結合の条件は、ON の後ろに書き、検索条件は WHERE に書きます。
同様に、下記のように WHERE にテーブル結合の条件を書くこともできますが、結合条件なのか検索条件なのかかがわかりづらくなるため、ON と WHERE を使い分けて書きます。

SELECT a.*, at.* 
FROM articles a, article_tag at
WHERE a.id = at.article_id AND a.id >= 3;

記事テーブルを中間テーブルに結合して、
記事に割り当てられているタグIDを参照できるようにする。

SELECT a.*, at.tag_id
FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id;

+----+--------+---------+--------+
| id | title  | content | tag_id |
+----+--------+---------+--------+
|  1 | Linux1 | 内容1   |      1 |
|  1 | Linux1 | 内容1   |      2 |
|  2 | Linux2 | 内容2   |      1 |
|  2 | Linux2 | 内容2   |      2 |
|  3 | Linux3 | 内容3   |      1 |
|  3 | Linux3 | 内容3   |      2 |
|  4 | PHP1   | 内容4   |      1 |
|  4 | PHP1   | 内容4   |      3 |
|  5 | PHP2   | 内容5   |      1 |
|  5 | PHP2   | 内容5   |      3 |
+----+--------+---------+--------+

次に、この結果とタグテーブルを結合して、タグ名を参照できるようにする。

SELECT a.*, at.tag_id, t.name
FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id
INNER JOIN tags t ON t.id = at.tag_id;

+----+--------+---------+--------+-------+
| id | title  | content | tag_id | name  |
+----+--------+---------+--------+-------+
|  1 | Linux1 | 内容1   |      1 | IT    |
|  1 | Linux1 | 内容1   |      2 | Linux |
|  2 | Linux2 | 内容2   |      1 | IT    |
|  2 | Linux2 | 内容2   |      2 | Linux |
|  3 | Linux3 | 内容3   |      1 | IT    |
|  3 | Linux3 | 内容3   |      2 | Linux |
|  4 | PHP1   | 内容4   |      1 | IT    |
|  4 | PHP1   | 内容4   |      3 | PHP   |
|  5 | PHP2   | 内容5   |      1 | IT    |
|  5 | PHP2   | 内容5   |      3 | PHP   |
+----+--------+---------+--------+-------+

最後に、タグ名を検索条件に追加すると、タグが「PHP」の記事一覧が取得できる。

SELECT a.*, at.tag_id, t.name
FROM articles a
INNER JOIN article_tag at ON a.id = at.article_id
INNER JOIN tags t ON t.id = at.tag_id
WHERE t.name = 'PHP';

+----+-------+---------+--------+------+
| id | title | content | tag_id | name |
+----+-------+---------+--------+------+
|  4 | PHP1  | 内容4   |      3 | PHP  |
|  5 | PHP2  | 内容5   |      3 | PHP  |
+----+-------+---------+--------+------+

また、IN を使って書くこともできます。
わかりやすいが、INNER JOIN に比べるとレスポンスが悪い。データ量が少ない場合は問題ない。

SELECT * 
FROM articles 
WHERE articles.id IN (
    SELECT article_id 
    FROM article_tag 
    WHERE tag_id IN (
        SELECT tags.id 
        FROM tags 
        WHERE NAME='PHP'
    )
);

+----+-------+---------+
| id | title | content |
+----+-------+---------+
|  4 | PHP1  | 内容4   |
|  5 | PHP2  | 内容5   |
+----+-------+---------+

中間テーブルのレコードがない articles のレコードの追加します。

insert into articles (id, title, content) values (6, 'タグなし', '内容6');

この状態で articles に article_tag を、id と tag_id を結合条件にして LEFT JOIN すると、articles に対応する article_tag がない行も取得される。
INNER JOIN の場合は、articles に対応する article_tag がない場合は取得されない。
また、articles がなくて、article_tag だけがある行も取得されない。articles が LEFT JOIN で結合されるテーブルとなるため。

SELECT a.*, at.tag_id
FROM articles a
LEFT JOIN article_tag at ON a.id = at.article_id;

+----+--------------+---------+--------+
| id | title        | content | tag_id |
+----+--------------+---------+--------+
|  1 | Linux1       | 内容1   |      1 |
|  1 | Linux1       | 内容1   |      2 |
|  2 | Linux2       | 内容2   |      1 |
|  2 | Linux2       | 内容2   |      2 |
|  3 | Linux3       | 内容3   |      1 |
|  3 | Linux3       | 内容3   |      2 |
|  4 | PHP1         | 内容4   |      1 |
|  4 | PHP1         | 内容4   |      3 |
|  5 | PHP2         | 内容5   |      1 |
|  5 | PHP2         | 内容5   |      3 |
|  6 | タグなし     | 内容6   |   NULL |
+----+--------------+---------+--------+